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.
- I figured it out!! I just moved the formula you made to a different section, multiplied those results by 24, and that converted it to decimal for me to be able to tally up the total. Thank you so much!!!!
- In the file I formatted those cells as [h].0 which displays hours and fractions of an hour. I forgot to mention that. But adding a =24*( previous formula ) will convert the actual value into #hours instead of just displaying it in hours. Glad it is working for you.
- 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.
