Time Duration Between Two Dates and Times

[marvin jones]

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.

 

[Gerald Higgins]

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

 

[marvin jones]

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,

 

[Gerald Higgins]

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 ?

 

[tyija1995]

With dates in cells A2 and A3 try:

D2:

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/09/2019 19:07[/TD]
[TD][/TD]
[TD][/TD]
[TD]21 Hours 03 Minutes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/09/2019 16:10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[marvin jones]

Hi Tyija,

Perfect, worked first time, exactly what i was looking for.

Thank you very much, unbelievable knowledge

 

[Gerald Higgins]

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 . . .

 

[marvin jones]

With dates in cells A2 and A3 try:

D2:

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/09/2019 19:07[/TD]
[TD][/TD]
[TD][/TD]
[TD]21 Hours 03 Minutes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/09/2019 16:10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[marvin jones]

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,

 

[Gerald Higgins]

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

d "days" hh "hours" mm "minutes"

and OK