Forum Discussion
Assistance with Excel Calculation Issues
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?
Preformatted to TIME, for the totalsPreformatted to Time for Start/FinishUsing the SUM formular to add the cells togther.Incorrect addition. Its meant to show 26:00Incorrect SUMThe cells for the complete total to add together. Gives the wrong numberFormular ViewTrace the relationships. But they are not adding up
Apply 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.
6 Replies
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)
- I applied your recommendation and it made it better by applying this formula to allow for the before midnight and after, cause many of the employees do work these hours =MOD(D5-C5, 1)
- Thank you so much! I really appreciate this extra bit of advise.
Apply 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.
- I applied your recommendation too and it fixed it. Thank you so much! THE RELIEF! lol
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"
- I applied your recommendation and it fixed it. Thank you so much! THE RELIEF! lol
