Adding time

[JimDandy]

I am trying to understand some reasons for odd results when I'm adding up a large number of hours. I have 3 columns with 70 rows each of various times ranging from 30 minutes to 345 hours. The cells are formatted [h]:mm to make it easier for users to input their hours. The total of this column is larger than just hours and minutes so it is formatted "m\m d\d h\h m\m". The column totals are shown below in both formats:

03m 13d 12h 00m (1764:00)
03m 04d 08h 30m (1544:30)
03m 01d 23h 45m (1487:45)

The odd part is when adding up the three columns I get what is visibly less than what it should be. Using the sum function of these 3 times I end up with:

07m 17d 20h 15m

Judging from the fact that each of the inputs are 3 months each, the total months alone should be at least 9 months, but it comes out as 7 months. In my head I think it should be "09m 19d 20h 15m" but I'm clearly doing something wrong.

[daddylonglegs]

Hello Jim.

m for month doesn't work very well in this context because it's a calendar month and not an "elapsed month". Firstly there's no month zero so it will always show at least 1 month, even if you have only 1:00 in the cell - following that logic the months are always 1 too high, so where you have 1764 hours that's actually 73.5 days, closer to 2 months 13 days, not 3 months, so all your values are 2 months and the total should be 6.

If you have months that add up to more than 12 you would also have problems because it won't show higher than that.....

It might be easier to show just hours.....or you can show total days and hours in another cell, assuming your data in A1, with this formula

=INT(A1)&"d "&TEXT(A1,"hh""h ""mm""m""")

[JimDandy]

Interesting point about the month feature. I actually set the format for a cell then pointed it at a blank cell and it showed just as you said, 1 month and 0 days, hours and minutes.

I ended up using your suggestion and just identifying the days and hours values rather than months...thank you

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.