Forum Discussion
Excel adding extra 24 hours
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.
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.
