Nathan Levy
New Member
Hello everyone.
I've been trying to death to create a spreadsheet to control overtime and stand-by hours. The problem starts when in a given time range, say "19:00" to "06:00, I need to extract 1, 2 or more slices of time, based on time-off and/or pay rules. I googled everything I could and also searched down this forum, but I had luck so far. I have IF statements, but really need some guidance to overcome this.
I uploaded the file showing what I'm trying to do, here at dropbox (I tried the forum's upload but it's not working for me don't know why). Columns G,H calculates the stand-by and mon-sat time off. I didn't manage to create the rules for column I (Mon-Sat_Pay) and J (Sun) because, they follow the same logic from column H.
I filled Column C and E with start and End times with all possible values the time range que have to create a validation on column L.
The rules are the following:
Overtime Rules
From To Sart End Action
mon Sat 0:00 6:00 Pay
mon Sat 5:00 9:00 TimeOff
Mon Sat 18:00 22:00 TimeOff
Mon Sat 22:00 24:00 Pay
Sun 0:00 24:00 Pay
Stand-by
From To Sart End Action
mon Sat 18:00 0:00 Pay
mon Sat 0:00 9:00 Pay
Sun 0:00 24:00 Pay
Again, any help you guys can give is going to be very much appreciated.
I've been trying to death to create a spreadsheet to control overtime and stand-by hours. The problem starts when in a given time range, say "19:00" to "06:00, I need to extract 1, 2 or more slices of time, based on time-off and/or pay rules. I googled everything I could and also searched down this forum, but I had luck so far. I have IF statements, but really need some guidance to overcome this.
I uploaded the file showing what I'm trying to do, here at dropbox (I tried the forum's upload but it's not working for me don't know why). Columns G,H calculates the stand-by and mon-sat time off. I didn't manage to create the rules for column I (Mon-Sat_Pay) and J (Sun) because, they follow the same logic from column H.
I filled Column C and E with start and End times with all possible values the time range que have to create a validation on column L.
The rules are the following:
Overtime Rules
From To Sart End Action
mon Sat 0:00 6:00 Pay
mon Sat 5:00 9:00 TimeOff
Mon Sat 18:00 22:00 TimeOff
Mon Sat 22:00 24:00 Pay
Sun 0:00 24:00 Pay
Stand-by
From To Sart End Action
mon Sat 18:00 0:00 Pay
mon Sat 0:00 9:00 Pay
Sun 0:00 24:00 Pay
Again, any help you guys can give is going to be very much appreciated.