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.