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

#### gazwilliamson

##### New Member
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

##### Active Member
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.

 15-Aug-2019 9:15:31 22-Aug-2019 10:33:03

<tbody>
</tbody>

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.

ABC
115-Aug-2019 9:15:3122-Aug-2019 10:33:037 days 1hr 17m 32s

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C1=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")

<tbody>
</tbody>

<tbody>
</tbody>

Is that what you wanted?

#### Special-K99

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

Last edited:

#### gazwilliamson

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

You're welcome!