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


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?



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


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)



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.