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

[alexw94]

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: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.

Any suggestions please?

[#2]

@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.

[#3]

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?

[#4]

@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?

[#5]

@HansVogelaar 

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 🙂

[#6]

@alexw94 

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

[#7]

@HansVogelaar 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.

[#8 · Accepted 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:

Workbook attached.