Display of a total of times that exceed 24 hours (with [h]:mm formatting) not working properly

[NewOrderFac33]

Good afternoon,

I have a range of cells that contain formulae that add up ranges of cells that also contain times.

My range returns the following results (formatted as hh:mm)

05:00
11:45
02:00
01:00
07:00

These add up to 26 hours and 45 minutes. However, my =SUM formula only returns 02:45 when formatted as hh:mm

So, to get over the 24 hour limit, I have formatted the cell containing my totaling formula as [h]:mm but now it displays 50:45!

Can anyone tell me where I'm going wrong, please?

Thanks in advance

Pete

 

[VoG]

I would double check that the format is [h]:mm - it returns the correct result for me

Excel Workbook
	A
1	05:00
2	11:45
3	02:00
4	01:00
5	07:00
6	26:45
Sheet5

 

[RJPotts]

Good afternoon,

I have a range of cells that contain formulae that add up ranges of cells that also contain times.

My range returns the following results (formatted as hh:mm)

05:00
11:45
02:00
01:00
07:00

These add up to 26 hours and 45 minutes. However, my =SUM formula only returns 02:45 when formatted as hh:mm

So, to get over the 24 hour limit, I have formatted the cell containing my totaling formula as [h]:mm but now it displays 50:45!

Can anyone tell me where I'm going wrong, please?

Thanks in advance

Pete

Given that the difference between what you're expecting and what you get is 24:00 I would hazard a guess that one of the cells in your first range is showing the incorrect result. E.g. maybe 01:00 should actually be 25:00? I would set the format of those cells to [h]:mm first, I think you may then find that the result really should be 50:45.

Best regards
Richard

 

[NewOrderFac33]

Yep, that's it - my original 5 formulae weren't formatted to [h]:mm, just the totals. When everything is formatted in that way, the correct results are displayed.
Thanks all for your help. I need some fresh air and a big bag of Haribo!