Time Duration Between Two Dates and Times

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
86
Hi,

What formula would i use in D2 to get the correct days, hours and minutes duration between cells A2 and A3?

A2 01/09/2019 19:07:30 PM D2 Formula to state 1 Day 11 Hours and 18 Minutes
A3 02/09/2019 16:10:50 PM

Thanks.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi, why is the result 1 day 11 hours and 18 minutes ?
Seems to me like it is 0 days 21 hours 3 mintues (and 20 seconds).

In principle, you can calculate the time difference by a simple
=A3-A2
 
Upvote 0
Hi Gerald,

Thanks for the reply.

Apologies, i probably had a formula that just didn't work properly.

It's how the data exports really and i just wanted to know how long a trailer had been in one place. It's just helpful for people to see e.g, 1 day, 11 hours and 18 minutes.

Thanks,
 
Upvote 0
OK that doesn't really help me I'm afraid.

With the two dates / times you provided, why is the answer 1 day 11 hours 18 minutes, and not 0 days 21 hours 3 minutes ?
Does the formula I suggested work for you ?
 
Upvote 0
With dates in cells A2 and A3 try:

D2:
Code:
IF(COUNTBLANK(A2:A3)>0,"",IF((A3-A2)<1,"",IF((A3-A2)<2,LEFT((A3-A2),FIND(".",(A3-A2))-1)&" Day ",LEFT((A3-A2),FIND(".",(A3-A2))-1)&" Days "))&IF(HOUR(A3)=HOUR(A2),"",IF(HOUR(A3)=HOUR(A2)+1,"1 Hour ",ROUNDDOWN(MID((A3-A2),FIND(".",(A3-A2)),99)*24,0)&" Hours "))&IF(MINUTE(A3)=MINUTE(A2),"",IF(MINUTE(A3)=MINUTE(A2)+1,"1 Minute",MID(DOLLARFR((A3-A2)*24,60),FIND(".",DOLLARFR((A3-A2)*24,60))+1,2)&" Minutes")))

Example:

ABCD
201/09/2019 19:0721 Hours 03 Minutes
302/09/2019 16:10

<tbody>
</tbody>
 
Upvote 0
OR, use a simple
=A3-A2
and format as Custom
d "days" hh "hours" mm "minutes"

which will display
0 days 21 hours 03 minutes
with the source data given.

Still don't see how you get 1 day 11 hours 18 minutes . . .
 
Upvote 0
With dates in cells A2 and A3 try:

D2:
Code:
IF(COUNTBLANK(A2:A3)>0,"",IF((A3-A2)<1,"",IF((A3-A2)<2,LEFT((A3-A2),FIND(".",(A3-A2))-1)&" Day ",LEFT((A3-A2),FIND(".",(A3-A2))-1)&" Days "))&IF(HOUR(A3)=HOUR(A2),"",IF(HOUR(A3)=HOUR(A2)+1,"1 Hour ",ROUNDDOWN(MID((A3-A2),FIND(".",(A3-A2)),99)*24,0)&" Hours "))&IF(MINUTE(A3)=MINUTE(A2),"",IF(MINUTE(A3)=MINUTE(A2)+1,"1 Minute",MID(DOLLARFR((A3-A2)*24,60),FIND(".",DOLLARFR((A3-A2)*24,60))+1,2)&" Minutes")))

Example:

ABCD
201/09/2019 19:0721 Hours 03 Minutes
302/09/2019 16:10

<tbody>
</tbody>

Hi Tyija - sorry, but when i have this formula for;

A10: 02/09/2019 08:58:55
A11: 02/09/2019 09:08:16

D10: 1 Hour 09 Minutes?
 
Upvote 0
OR, use a simple
=A3-A2
and format as Custom
d "days" hh "hours" mm "minutes"

which will display
0 days 21 hours 03 minutes
with the source data given.

Still don't see how you get 1 day 11 hours 18 minutes . . .

Hi Gerald,

I must have used a wrong formula before to get that.

Sorry - the only custom format i can see is dd/mm/yyyy hh:mm?

What am i missing?

Thanks,
 
Upvote 0
The point about a custom format is that you can enter anything you like as the format.
What you input might not work, but that's beside the point.

In the format cells dialog box . . .
Under Category, choose Custom
In the Type box, input
Code:
d "days" hh "hours" mm "minutes"
and OK
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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?

Disable AdBlock

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
Back
Top