Forum Discussion
Excel adding extra 24 hours
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?
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)/60Try it and see if it resolves your problem.
3 Replies
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)/60Try it and see if it resolves your problem.
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!
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.
