Excel adding extra 24 hours

[kapoosht]

I'm running a pivot table on a data set where I am summing up time spent per ticket (i.e. one ticket could have 3 time logs against it and I need the sum of all time logged for the ticket). Every ticket is calculated perfectly, except for 2 cells shown in my screenshot below.

The "Row Labels" column clearly shows 01-01-1900 3:00AM, which I understand to be 27 hours. For some reason when excel is summing up this particular value, the sum gets converted to 27 + 24 hours. Same goes for the next row which is 01-01-1900 8:00AM. 

I have other cells not shown in this screenshot in the data set that falls within the same range e.g. between 24-48 hours, and they are all calculated correctly. I've checked number formatting etc, and all looks to be the same.

Any idea what could be the reason?

[#2 · Accepted solution]

@kapoosht Calculating with time is difficult in Excel. 1 hour = 1/24th of one day, 1 minute = 1/60th of one hour or 1/1440th of one day. When your duration goes over 24 hours (=1 day), Excel starts counting time from scratch. Thus, 25 hours equals 1 day plus 1 hour. Custom formatting allows you to display this as 25:00:00, but the time portion is still only 1 hour.

If you want to calculate with hours, it's better to use a decimal representation of it. E.g. 6hrs and 15 minutes = 6.25 hrs. Adding up 4 instances of 6.25 then becomes 25.0 (hours). Now, I never tried to sum time entries in a pivot table, but I'm not surprised that it creates problems. Calculating with numbers, though, alway works. Suppose your ticket time is in A1, the following formula will translate it to a decimal number for the hours. Use these in the pivot table in stead.

=DAY(A1)*24+HOUR(A1)+MINUTE(A1)/60

  Try it and see if it resolves your problem.

[#3]

@Riny_van_Eekelen your suggestion works - thanks!

However I'm still quite curious as to why all my calculations (i've been using this addition of time for over 2 years now) have been working perfectly so far except for these 2 cells... nevertheless, appreciate the quick response and fix.

Cheers!

[#4]

@kapoosht Glad it worked. Did some testing just now but could not replicate the error you described when adding up time entries in a pivot table. So, it will remain a mystery why it suddenly stopped working for you.