Hi All,
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).
There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.
Upper Bound: 22:00 (10pm)
Lower Bound: 7:30 (7.30am)
B10:Shift Start - 11:30PM
C10: Shift Finish: 5:00Am
D10: Total Time - =MOD($C10-$B10,1)
E10: Time In Core Period
=IF($C10>=$B10,MIN($C10,UpperBound)-MAX($B10,LowerBound),MAX(0,$C10-LowerBound))
F10: Time Outside Core period =$D10-$E10
I have attached a spreadsheet to help show the problem.
Any help would be greatly appreciated? I look forward to seeing your possible solutions!
I'm not sure on the exact rules - but I've posted this question on another forum also:
http://www.excelforum.com/excel-gen...e-within-a-period-when-crossing-midnight.html
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).
There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.
Upper Bound: 22:00 (10pm)
Lower Bound: 7:30 (7.30am)
B10:Shift Start - 11:30PM
C10: Shift Finish: 5:00Am
D10: Total Time - =MOD($C10-$B10,1)
E10: Time In Core Period
=IF($C10>=$B10,MIN($C10,UpperBound)-MAX($B10,LowerBound),MAX(0,$C10-LowerBound))
F10: Time Outside Core period =$D10-$E10
Code:
I have attached a spreadsheet to help show the problem.
Any help would be greatly appreciated? I look forward to seeing your possible solutions!
I'm not sure on the exact rules - but I've posted this question on another forum also:
http://www.excelforum.com/excel-gen...e-within-a-period-when-crossing-midnight.html
