Excel - Subtracting 2 dates/times (DD/MM/YY HH:MM:SS) to find the difference

[gazwilliamson]

Hi there,

Read a couple of posts on this, and struggling to get my head around it. Sorry if this has been answered before.

My data source outputs dates/times as 22/08/2019 10:33:03 in one cell, formatted as (dd-mm-yyyyhh:mm:ss).

What im looking to find out, is the difference in timebetween two dates.
For example
The difference between B1 = 22/08/2019 10:33:03 and A1= 15/08/2019 09:15:31

Ideally, I need C1 to show this difference as e.g. 7 days 1hr17m 33s (or as close to this as I can get it)

Any help would be greatly appreciated
Thanks

 

[Toadstool]

Hi Gaz,

First I'm going to format those two cells as Custom dd-mmm-yyyy h:mm:ss so it's easier to understand both sides of the pond.

[TABLE="class: grid, width: 444"]
<tbody>[TR]
[TD="width: 138, align: right"]15-Aug-2019 9:15:31[/TD]
[TD="width: 145, align: right"]22-Aug-2019 10:33:03[/TD]
[/TR]
</tbody>[/TABLE]

If I subtract B1 from B2 into a cell with a General format then I'll see 7.053842593 as that's seven days and the fractional day which is how Excel holds time. Note: Hopefully your maths is wrong and the difference should actually end as 32 seconds.

I use INT to get the 7 days.

I can now use the MOD function to get the time fraction 0.053842593 with =MOD(B1-A1,1)

To construct cell C1 I can get the text representation of the time and use MID to pull out the relevant numbers. I'll then wrap the whole thing in a TRIM to strip out unnecessary spaces.

	A	B	C
7 days 1hr 17m 32s

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]15-Aug-2019 9:15:31[/TD]
[TD="align: right"]22-Aug-2019 10:33:03[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=TRIM(INT(B1-A1)&" days "&MID(TEXT(MOD(B1-A1,1)," h: m: s"),1,2)&"hr "&MID(TEXT(MOD(B1-A1,1)," h: m: s"),5,2)&"m "&MID(TEXT(MOD(B1-A1,1)," h: m: s"),9,2)&"s")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Is that what you wanted?

 

[Special-K99]

@Toadstool: Crikey, that's the clearest explanation of a solution I've ever seen on this board! Well done!

 

[Toadstool]

Thanks for your kind comment!

 

[gazwilliamson]

That's exactly what I was looking for - thanks for the clear explanation

 

[Toadstool]

You're welcome!