# Mean time between dates. Multiple columns.

#### engine_man3

##### New Member
I'm sure this is a simple solution, but I can't seem to wrap my brain around the easiest way to do this.

I need to know the time passed between the start (date and time) and the finish (date and time). Keep in mind, sometimes the time goes over multiple days, so the date must be factored in.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Something like this in Column N copied down

=TEXT( (M2-K2) + (L2-J2), "[hh]:mm:ss")

Or just:

=SUM(L2:M2)-SUM(J2:K2)

formatted as [hh]:mm:ss

Use the formula:
=L2-J2+M2-K2

Then format the column/cells with that formula to custom format of: [h]:mm:ss

To custom format, highlight the cells then hit CTRL & 1 then put in the above formula in the type text box.

Something like this:

=DAY(L1-J1) & " Day(s) " & HOUR(M1-K1) & " Hour(s) " & MINUTE(M1-K1) & " Minute(s)"

or to overkill it further...

=DAY(L1-J1) & " Day(s) " & HOUR(M1-K1) & " Hour(s) " & MINUTE(M1-K1) & " Minute(s) " & SECOND(M1-K1) & " Second(s)"

Last edited:
Or just:

=SUM(L2:M2)-SUM(J2:K2)

formatted as [hh]:mm:ss

This is what I went with. For some reason I didn't think it would carry time over dates. Thanks to all for your help

Replies
0
Views
200
Replies
3
Views
220
Replies
0
Views
528
Replies
2
Views
531
Replies
7
Views
242

1,203,072
Messages
6,053,377
Members
444,659
Latest member
vuphihung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back