Forum Discussion
Solved
Time Sheet Help
I'm going crazy here. I've been working on this Excel spreadsheet for 4 hours and I can't seem to figure this out. All I want to do is calculate hours. I have example Start and End times entered a...
In the attached I used the following:
=SUMIFS(E6:AC6,D6:AB6,"to")-SUMIFS(C6:AA6,D6:AB6,"to")+SUM((C6:AA6>E6:AC6)*(D6:AB6="to"))Which relies on the middle cell to be "to" for a valid entry. I added that last SUM term because the second line has hours 9PM - 7AM which I ASSUME means you want the hours worked to go past midnight and into the next day.
That said, I agree with Detlef_Lewin that there are much better ways to organize a timesheet.
In the attached I used the following:
=SUMIFS(E6:AC6,D6:AB6,"to")-SUMIFS(C6:AA6,D6:AB6,"to")+SUM((C6:AA6>E6:AC6)*(D6:AB6="to"))Which relies on the middle cell to be "to" for a valid entry. I added that last SUM term because the second line has hours 9PM - 7AM which I ASSUME means you want the hours worked to go past midnight and into the next day.
That said, I agree with Detlef_Lewin that there are much better ways to organize a timesheet.
Unfortunately I'm having to work within the constraints of how they've always organized it. Before they were manually calculating the hours, and I'm wanting to make it to where they don't have to do that anymore without changing the formatting (they're resistant to change).
The formula you came up with is pure brilliance!! It works within the constraints I have. There is only one problem. For some reason it's not calculating half-hours. For example, if someone has a total of 35.5 hours worked, it's rounding down to 35 hours for some reason. I'm still digging into this to see if I can figure it out. Thank you so much for your help.
The formula you came up with is pure brilliance!! It works within the constraints I have. There is only one problem. For some reason it's not calculating half-hours. For example, if someone has a total of 35.5 hours worked, it's rounding down to 35 hours for some reason. I'm still digging into this to see if I can figure it out. Thank you so much for your help.