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

#### NewOrderFac33

##### Well-known Member
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?

Pete

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I would double check that the format is [h]:mm - it returns the correct result for me

Excel Workbook
A
105:00
211:45
302:00
401:00
507:00
626:45
Sheet5

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?

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

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!

Replies
2
Views
150
Replies
10
Views
504
Replies
0
Views
622
Replies
1
Views
187
Replies
3
Views
88

1,196,386
Messages
6,014,984
Members
441,862
Latest member
GarthZAR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back