Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function that separates hours worked in work shifts that overlap
We have temporary employees; sometimes an employee's hours will run over into
the next shift. Based on noting the employee's hours and the set time of each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00 am), I need a function that will automatically sort out which hours are First Shift, which are Second Shift, and so forth. Furthermore, I want the function to provide the actual number of hours worked on that shift in a separate cell. That is, for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4 hours, etc. I'm thinking this will probably be a nested function, I haven't been able to locate a function that will allow me to do the intial calculation. Any suggestions? Thank you. -- Katy Garrabrant-Hogan PC Training Coordinator |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function that separates hours worked in work shifts that overlap
Hi,
A starter: For First shift hours use =CalculateShiftTimes(starttime, endtime,1) ForSecond shift hours use =CalculateShiftTimes(starttime, endtime,2) There is no check for valid hours i.e. start before 09:00 or finish after midnight . Function CalculateShiftTimes(sTime, eTime, rtn) fsTime = 0 ' First Shift Hours ssTime = 0 ' Second Shift hours sb = 0.70833333 ' Shift change time i.e 17:00 hours If eTime = 0 Then eTime = 1 ' Shift finishes at midnight If eTime <= sb Then ' Finishes before 17:00 fsTime = eTime - sTime ' First shift hours Else ' Finishes in second shift fsTime = sb - sTime ' First Shift Hours ssTime = eTime - sb ' Second shift hours End If If rtn = 1 Then rtnVal = fsTime Else rtnVal = ssTime End If CalculateShiftTimes = rtnVal End Function HTH "Katybug1964" wrote: We have temporary employees; sometimes an employee's hours will run over into the next shift. Based on noting the employee's hours and the set time of each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00 am), I need a function that will automatically sort out which hours are First Shift, which are Second Shift, and so forth. Furthermore, I want the function to provide the actual number of hours worked on that shift in a separate cell. That is, for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4 hours, etc. I'm thinking this will probably be a nested function, I haven't been able to locate a function that will allow me to do the intial calculation. Any suggestions? Thank you. -- Katy Garrabrant-Hogan PC Training Coordinator |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function that separates hours worked in work shifts that ove
Thank you! It's very helpful. I haven't written a macro in a while so this
will be good practice. -- Katy Garrabrant-Hogan PC Training Coordinator "Toppers" wrote: Hi, A starter: For First shift hours use =CalculateShiftTimes(starttime, endtime,1) ForSecond shift hours use =CalculateShiftTimes(starttime, endtime,2) There is no check for valid hours i.e. start before 09:00 or finish after midnight . Function CalculateShiftTimes(sTime, eTime, rtn) fsTime = 0 ' First Shift Hours ssTime = 0 ' Second Shift hours sb = 0.70833333 ' Shift change time i.e 17:00 hours If eTime = 0 Then eTime = 1 ' Shift finishes at midnight If eTime <= sb Then ' Finishes before 17:00 fsTime = eTime - sTime ' First shift hours Else ' Finishes in second shift fsTime = sb - sTime ' First Shift Hours ssTime = eTime - sb ' Second shift hours End If If rtn = 1 Then rtnVal = fsTime Else rtnVal = ssTime End If CalculateShiftTimes = rtnVal End Function HTH "Katybug1964" wrote: We have temporary employees; sometimes an employee's hours will run over into the next shift. Based on noting the employee's hours and the set time of each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00 am), I need a function that will automatically sort out which hours are First Shift, which are Second Shift, and so forth. Furthermore, I want the function to provide the actual number of hours worked on that shift in a separate cell. That is, for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4 hours, etc. I'm thinking this will probably be a nested function, I haven't been able to locate a function that will allow me to do the intial calculation. Any suggestions? Thank you. -- Katy Garrabrant-Hogan PC Training Coordinator |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to calculate total hours worked in one week. | Excel Worksheet Functions | |||
Add shifts worked on a second worksheet | Excel Discussion (Misc queries) | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
Formula to work out hours worked | Excel Discussion (Misc queries) | |||
Need a function that separates over-lapping work shift hours. | Excel Worksheet Functions |