Time Sheet Help

[KevinCommunitas]

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.

[#2]

@KevinCommunitas 

There are many DO NOTs in your workbook.

[#3 · Accepted solution]

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.

[#4]

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.

[#5]

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!!!!

[#6]

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.