Apr 13 2020 07:25 AM
Apr 13 2020 07:25 AM
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?
Apr 13 2020 08:49 AMSolution
@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.
Try it and see if it resolves your problem.
Apr 13 2020 07:16 PM
@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.