SOLVED

How to subtract the difference between 2 cells that contain DD:MM:YYYY HH:MM?

Copper Contributor

Hi Need help please.

 

What formula do do to get the difference between 2 DD:MM:YYYY HH:MM in HH:MM. 

 

For instance - I tried simply subtracting the cells but I get this:

 

26/09/2022 13:0928/09/2022 13:23 00:14

 

The answer should be 48:14 and my formula does not seem to take into account there is 2 days worth of time to calculate as well as the 14 minutes.

 

Any suggestions please?

7 Replies

@alexw94 

Set the number format of the cell with the difference to the custom format [hh]:mm

The [ ] around hh instruct Excel to display the time as duration instead of as clock time.

This has not made any difference. It is still showing as 00:14 where as it should be 48:14. Anyone got anymore suggestions please?

@alexw94 

 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

 

I attach a screenshot of small sample of the table, the red line highlighted is where there is an issue. As this line is subtracting between 2 days as well as the time difference, it is not returning the correct answer it should be 48 hours (with it being 2 days and 14 minutes). I need a formula that will return the true HH:MM's between these 2 dates and times, the formula I have used B2-A2 doesn;t seem to recognise that there is a date within the field and is not subtracting the difference. I need to have the HH:MM between 2 dates and times. ScreeScreenshot Time difference data.pngnshot attacted :)

@alexw94 

I'm afraid that doesn't help. I repeat the request from my previous reply.

@Hans Vogelaar I attach, formula is in there as you can see,but as mentioned above this formula is not taking into account when there is a difference in date as well as time. Please note the format is for Date is United Kingdom. Thank you.

best response confirmed by alexw94 (Copper Contributor)
Solution

@alexw94 

Thanks!

The number format is hh:mm, not [hh]:mm as I suggested.

If you change the number format to the custom format [hh]:mm, you'll see the correct duration:

HansVogelaar_0-1683624985685.png

Workbook attached.

1 best response

Accepted Solutions
best response confirmed by alexw94 (Copper Contributor)
Solution

@alexw94 

Thanks!

The number format is hh:mm, not [hh]:mm as I suggested.

If you change the number format to the custom format [hh]:mm, you'll see the correct duration:

HansVogelaar_0-1683624985685.png

Workbook attached.

View solution in original post