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

gazwilliamson

New Member
Joined
Jun 4, 2019
Messages
6
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

Active Member
Joined
Mar 5, 2018
Messages
340
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:3122-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?
 

Forum statistics

Threads
1,082,505
Messages
5,365,965
Members
400,864
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top