Feb 27 2023 01:55 AM
Hello.
I'm hoping to get some help on this problem I'm having, I cannot for the life of me understand why my spreadsheet is not calculating simple attraction using the =SUM() or =. I have tried googling the answer, but I am completely baffled. So here I am.
I've made a simple employee timesheet, Start/Finish, total hours for each employee, and a total for all employees. Sounds straightforward, well excel doesn't like me very much or maybe it cant calculate, I'm yet to be convinced.
I have prepared the cells as TIME, that was my first step, I added in the total 'Finish Cell' - 'Start Cell' example '=D5-C5' and copied that down to the other cells below. I then used the auto sum =SUM() and selected the above cells to calculate. But I keep getting the wrong calculation.
To top it off. The Total Hours for all Employees don't get any better either. Again, simple math! I just want to add all the employee's total hours together. But it gives something different.
I have tried, starting a new excel form, I have tried removing all formatting and clearing all the cells of their data, but no matter how many tries, It continues to not add up simple math.
The Calculation Options under the Formula tabs are set to 'Automatic'.
Please, what am I doing wrong?
Feb 27 2023 03:13 AM
I have only looked at the first part of your post but, there, the problem appears to be that the total of hours given by
= SUM(finish - start)
is 1.08333 which means 1 and ¹⁄₁₂ days. Formatted as time this shows only the 2 hours. The number format to use is "[h]:mm" which will show the result in the form "26:00"
Feb 27 2023 03:14 AM
SolutionApply the custom number format [h]:mm to the cells that sum times.
The square brackets [ ] around h instruct Excel to display the total as cumulative time (that can be more than 24 hours) instead of as clock time.
Feb 27 2023 03:24 AM
Also, if the start time could be before midnight and the end time after midnight (for example someone worked from 8:00 PM to 4:00 AM), change the formula =D5-C5 to =MOD(D5-C5, 1)
Feb 27 2023 03:43 AM
Feb 27 2023 03:43 AM
Feb 27 2023 09:05 PM
Feb 27 2023 03:14 AM
SolutionApply the custom number format [h]:mm to the cells that sum times.
The square brackets [ ] around h instruct Excel to display the total as cumulative time (that can be more than 24 hours) instead of as clock time.