Forum Discussion
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 already. I want to calculate day by day the number of hours they worked, then tabulate the total hours worked in the Total Hrs column.
Or, if it's possible, I'd prefer to NOT calculate day by day, remove the "Hrs" columns after each day, and just use the Total Hrs column to tabulate all of the hours worked for a given week.
Both options seem to be beyond my abilities. I've been searching online for different types of formulas to potentially use, but nothing is seeming to work. I got VERY close once, but the hours coming up were negative hours, and when I tried correcting it to not be negative hours, the formula (MOD) wasn't working for me like it was for others.
Can anybody help me stop pulling my hair out here? I'm already bald as it is, so there's not much left and I'd like to keep what I have haha.
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.
5 Replies
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!!!!
