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

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,275
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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! :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top