Find trip duration in days, from a cell with multiple dates

**alexy2k** · 10-03-2020, 04:21 AM

Hi all,
Need your expert advice on this. I have attached the sample excel file.

I am preparing a business travel spend report for a client.

And in one of the fields, I need to include the total trip duration, from the available data given to me.

(Screenshot below)

Thanks,
Alex

Capture.JPG

**XOR LX** · 10-03-2020, 05:09 AM

Hi,

=MIN(ABS(MOD(MMULT(AGGREGATE({14,15},6,FILTERXML("<a><b>"&SUBSTITUTE(C2," ","</b><b>")&"</b></a>","//b[string-length(.)=5]"),1),{1;-1}),{-1,1}*366)))

Regards

**alexy2k** · 10-03-2020, 05:34 AM

Thank you so very much !! It worked for me.

**XOR LX** · 10-03-2020, 05:53 AM

You're welcome!

Regards

**Bo_Ry** · 10-03-2020, 06:36 AM

@XOR LX nice new XML trick

string-length(.)

Another one for start date is always at 2nd from the left and end date is always at 2nd from right.

=MOD(LEFT(RIGHT(C2,10),5)-MID(C2,5,5),"31dec"-"1jan"+1)

**XOR LX** · 10-03-2020, 07:50 AM

Originally Posted by Bo_Ry

@XOR LX nice new XML trick

string-length(.)

Indeed!

Originally Posted by Bo_Ry

=MOD(LEFT(RIGHT(C2,10),5)-MID(C2,5,5),"31dec"-"1jan"+1)

I like the "31dec"-"1jan"+1 part - clever!

Regards

Find trip duration in days, from a cell with multiple dates

LinkBack

Thread Tools

Rate This Thread

Display

Find trip duration in days, from a cell with multiple dates

Re: Find trip duration in days, from a cell with multiple dates

Re: Find trip duration in days, from a cell with multiple dates

Re: Find trip duration in days, from a cell with multiple dates

Re: Find trip duration in days, from a cell with multiple dates

Thread Information

Users Browsing this Thread

Similar Threads

Need help with the DAYS function to find number of days between two dates then multiply.

[SOLVED] Determining how many days between dates fall within a specified duration and how many

Change Start dates upon duration days change in Project timeline calculation.

Count duration between days If another cell contains information

Excel 2007 : Duration Between Two Dates In Month, Week And Days

how to calculate duration with first, last date and multiple dates in between

Duration of time for two dates in days, hours and minutes

Bookmarks

Bookmarks

Posting Permissions