SOLVED

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

Occasional Contributor

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

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:09 28/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.

7 Replies

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

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.

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

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?

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

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?

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

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

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

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

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

@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 (Occasional Contributor)
Solution

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

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:

Workbook attached.