If Function for a Time set as overtime

[MattG]

I am trying to write a function that will work as an if over a certain time of day it is overtime or between a certain time period it is straight time.

I work in even production and many times when doing union schedules there are different caveats on the rate hours and working stipulations. I have made a workbook, see attached as a dumbed down version for this example, that has two sheets. First sheet is a simple schedule. It will list the number of people needed, their start time and end time. I typically repeat that chunk 3 times because often time we do a start of the day check in, a lunch, then an afternoon work call, then sometimes a dinner, then a evening call.

For this example all hours Monday through Friday 8am to 5pm would be straight time. All hours on saturday and sunday and anything from 5pm-8am would be overtime. Is there in the attached example a way I could write an if function in cell F4 to look at the times in C4 & D4 to determine if they are overtime or straight time. My hope is I can on the second sheet labeled rates that I can fill in the start time/end time for the given straight or overtime starting and ending points.

What really confuses me with doing this is the face that saturday and sunday are always overtime no matter what the rate.

I definitely can do this manually. In my real sheet I have it just look for rate amount based on the rate type being OT or ST. I typically just manually enter that in there. No real biggie, but in trying to expand my excel knowledge I think there would be a way to actually do this and hoping I can figure that out.

Thanks in advance for any insight.

Matt

[Glenn Kennedy]

Are the straight and overtime start/finish times the SAME for all staff?

[MattG]

No not necessarily

I may have staff A from 8-5 but keep staff be from 8am - 10pm or whatever.

Would that make a difference?

Matt

[Glenn Kennedy]

Ah no. Communication problem.

are the times in Rates C2 & D2, F2 & G2 the same for all staff, or do those timea apply only to A. Is it possible that some staff are employed with different normal working hours?

[MattG]

No the times are not the same