SOLVED

Excel adding extra 24 hours

%3CLINGO-SUB%20id%3D%22lingo-sub-1302677%22%20slang%3D%22en-US%22%3EExcel%20adding%20extra%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1302677%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20running%20a%20pivot%20table%20on%20a%20data%20set%20where%20I%20am%20summing%20up%20time%20spent%20per%20ticket%20(i.e.%20one%20ticket%20could%20have%203%20time%20logs%20against%20it%20and%20I%20need%20the%20sum%20of%20all%20time%20logged%20for%20the%20ticket).%20Every%20ticket%20is%20calculated%20perfectly%2C%20except%20for%202%20cells%20shown%20in%20my%20screenshot%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%22Row%20Labels%22%20column%20clearly%20shows%2001-01-1900%203%3A00AM%2C%20which%20I%20understand%20to%20be%2027%20hours.%20For%20some%20reason%20when%20excel%20is%20summing%20up%20this%20particular%20value%2C%20the%20sum%20gets%20converted%20to%2027%20%2B%2024%20hours.%20Same%20goes%20for%20the%20next%20row%20which%20is%2001-01-1900%208%3A00AM.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22kapoosht_0-1586787576670.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183942i370BAE3C676DC287%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22kapoosht_0-1586787576670.png%22%20alt%3D%22kapoosht_0-1586787576670.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20other%20cells%20not%20shown%20in%20this%20screenshot%20in%20the%20data%20set%20that%20falls%20within%20the%20same%20range%20e.g.%20between%2024-48%20hours%2C%20and%20they%20are%20all%20calculated%20correctly.%20I've%20checked%20number%20formatting%20etc%2C%20and%20all%20looks%20to%20be%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20what%20could%20be%20the%20reason%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1302677%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1302840%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20adding%20extra%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1302840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F619412%22%20target%3D%22_blank%22%3E%40kapoosht%3C%2FA%3E%26nbsp%3BCalculating%20with%20time%20is%20difficult%20in%20Excel.%201%20hour%20%3D%201%2F24th%20of%20one%20day%2C%201%20minute%20%3D%201%2F60th%20of%20one%20hour%20or%201%2F1440th%20of%20one%20day.%20When%20your%20duration%20goes%20over%2024%20hours%20(%3D1%20day)%2C%20Excel%20starts%20counting%20time%20from%20scratch.%20Thus%2C%2025%20hours%20equals%201%20day%20plus%201%20hour.%20Custom%20formatting%20allows%20you%20to%20display%20this%20as%2025%3A00%3A00%2C%20but%20the%20%3CSTRONG%3Etime%3C%2FSTRONG%3E%20portion%20is%20still%20only%201%20hour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20calculate%20with%20hours%2C%20it's%20better%20to%20use%20a%20decimal%20representation%20of%20it.%20E.g.%206hrs%20and%2015%20minutes%20%3D%206.25%20hrs.%20Adding%20up%204%20instances%20of%206.25%20then%20becomes%2025.0%20(hours).%20Now%2C%20I%20never%20tried%20to%20sum%20time%20entries%20in%20a%20pivot%20table%2C%20but%20I'm%20not%20surprised%20that%20it%20creates%20problems.%20Calculating%20with%20numbers%2C%20though%2C%20alway%20works.%20Suppose%20your%20ticket%20time%20is%20in%20A1%2C%20the%20following%20formula%20will%20translate%20it%20to%20a%20decimal%20number%20for%20the%20hours.%20Use%20these%20in%20the%20pivot%20table%20in%20stead.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DDAY(A1)*24%2BHOUR(A1)%2BMINUTE(A1)%2F60%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%20Try%20it%20and%20see%20if%20it%20resolves%20your%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1304274%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20adding%20extra%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1304274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Byour%20suggestion%20works%20-%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20I'm%20still%20quite%20curious%20as%20to%20why%20all%20my%20calculations%20(i've%20been%20using%20this%20addition%20of%20time%20for%20over%202%20years%20now)%20have%20been%20working%20perfectly%20so%20far%20except%20for%20these%202%20cells...%20nevertheless%2C%20appreciate%20the%20quick%20response%20and%20fix.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1304821%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20adding%20extra%2024%20hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1304821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F619412%22%20target%3D%22_blank%22%3E%40kapoosht%3C%2FA%3E%26nbsp%3BGlad%20it%20worked.%20Did%20some%20testing%20just%20now%20but%20could%20not%20replicate%20the%20error%20you%20described%20when%20adding%20up%20time%20entries%20in%20a%20pivot%20table.%20So%2C%20it%20will%20remain%20a%20mystery%20why%20it%20suddenly%20stopped%20working%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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. 

 

kapoosht_0-1586787576670.png

 

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?

 

3 Replies
best response confirmed by kapoosht (New Contributor)
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.

@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.