Issues with IF function relating to time sheet/OT (XL13)

[crissimorris]

https://docs.google.com/spreadsheets/d/1L16ntEDMBukQT-NGXVpdwFhni4Eu4r54uEFMYgcTnuM/edit?usp=sharing

Hopefully my link worked -

This is a time sheet I'm creating for work that tracks daily hours on a job. Overtime is any time before 7:00am or after 3:30pm Monday-Friday and all day Saturday and Sunday. I've spent 8 hours trying to get all the formulas set up so the spreadsheet becomes just data entry for my Administrative Clerk. I think I've figured out how to get the formula to work for the weekdays and I THOUGHT I had it figured out for Saturday and Sunday. Then I entered a date that started at 5:50pm on Saturday. For some reason, it has an issue with those start/end times. I don't know what else to do - help??

My issues (I think) are columns V,W,X.

Any other insight to make the formulas less complicated in the whole workbook would be appreciated.

Thank you!

 

[Scott Huish]

First off, if you are going to use time in a string, you have to coerce it back to a time value, usually by adding 0:

=IF(U11=0,"",MAX(0,MIN($L11,"15:30"+0)-MAX($E11,"7:00"+0))*1440)

But your formula results in a negative amount, and I'm not really sure what you expect for results.
Columns V,W,X also has 3 formulas but only 1 heading, what are those columns for exactly?

Your W11 columns shows 360 minutes which is equal to the total of 6 hours in U11.
It would help if you could explain what results you are expecting and why.

 

[crissimorris]

First off, if you are going to use time in a string, you have to coerce it back to a time value, usually by adding 0:

=IF(U11=0,"",MAX(0,MIN($L11,"15:30"+0)-MAX($E11,"7:00"+0))*1440)

But your formula results in a negative amount, and I'm not really sure what you expect for results.
Columns V,W,X also has 3 formulas but only 1 heading, what are those columns for exactly?

Your W11 columns shows 360 minutes which is equal to the total of 6 hours in U11.
It would help if you could explain what results you are expecting and why.

For Row 11, the day of the week is Saturday. All day Saturday should be overtime. So I am expecting that cell X11 would return 360 minutes of Overtime. I know that the formulas are complicated (and maybe duplicated?), but this was the only way I could get the numbers to work. What I don't understand is that if you look at Row 17, which is Sunday, the formulas appear to work correctly and give me 480 minutes worked overtime in cell X17.

Does that help explain?