Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
Hi there,
I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
Hi,
Suppose they work 5.25 hours what do you give them for a lunch break 1/2 hour, 1/4 hour or something else? If they work an 8.5 hour shift do you count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work? If they complete an 8 hour shift at 6PM but haven't taken lunch do you consider the lunch (6:00-6:30 PM) as night shift? -- Thanks, Shane Devenshire "Sara" wrote: Hi there, I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
Lunch breaks are always 1/2 hour long.
A shift of 5.25 hours doesn't exist - though I have seen a 5.5 hour shift. An 8.5 hour shift is 8 hours work, 1/2 hour lunch. The break at 5 hours is where the rule book states the lunch must be calculated from for pay purposes, however practise generally puts it where ever they want. Nightrate is only ever valid for hours worked between 8pm and 6am. So if a person works from 1700 - 01:30, I need the calc to show 5 hours nightrate 17:00 - 20:00 normal rate (3 hours) 2000 - 22:00 nightrate (2 hours) 22:00 - 22:30 lunch unpaid 2230 - 0130 nightrate (3 hours) Am I asking excel to do too much? "ShaneDevenshire" wrote: Hi, Suppose they work 5.25 hours what do you give them for a lunch break 1/2 hour, 1/4 hour or something else? If they work an 8.5 hour shift do you count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work? If they complete an 8 hour shift at 6PM but haven't taken lunch do you consider the lunch (6:00-6:30 PM) as night shift? -- Thanks, Shane Devenshire "Sara" wrote: Hi there, I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
No your not asking it to do too much, but experience tells me that these
benefits calculation can turn out to be a lot more complicate than initially stated. I have constructed timesheet/pay worksheets that would make your hair curl because there were so many variations to deal with. For those types of problem spreadsheet formulas were just not possible and I had to write VBA custom functions for everthing. Well I look over all of your responses and see what I can come up with. -- Thanks, Shane Devenshire "Sara" wrote: Lunch breaks are always 1/2 hour long. A shift of 5.25 hours doesn't exist - though I have seen a 5.5 hour shift. An 8.5 hour shift is 8 hours work, 1/2 hour lunch. The break at 5 hours is where the rule book states the lunch must be calculated from for pay purposes, however practise generally puts it where ever they want. Nightrate is only ever valid for hours worked between 8pm and 6am. So if a person works from 1700 - 01:30, I need the calc to show 5 hours nightrate 17:00 - 20:00 normal rate (3 hours) 2000 - 22:00 nightrate (2 hours) 22:00 - 22:30 lunch unpaid 2230 - 0130 nightrate (3 hours) Am I asking excel to do too much? "ShaneDevenshire" wrote: Hi, Suppose they work 5.25 hours what do you give them for a lunch break 1/2 hour, 1/4 hour or something else? If they work an 8.5 hour shift do you count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work? If they complete an 8 hour shift at 6PM but haven't taken lunch do you consider the lunch (6:00-6:30 PM) as night shift? -- Thanks, Shane Devenshire "Sara" wrote: Hi there, I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
How about simplifying things?
What would be the formula if we don't take the lunch break into consideration? I can then adapt the roster's to break for lunch "ShaneDevenshire" wrote: No your not asking it to do too much, but experience tells me that these benefits calculation can turn out to be a lot more complicate than initially stated. I have constructed timesheet/pay worksheets that would make your hair curl because there were so many variations to deal with. For those types of problem spreadsheet formulas were just not possible and I had to write VBA custom functions for everthing. Well I look over all of your responses and see what I can come up with. -- Thanks, Shane Devenshire "Sara" wrote: Lunch breaks are always 1/2 hour long. A shift of 5.25 hours doesn't exist - though I have seen a 5.5 hour shift. An 8.5 hour shift is 8 hours work, 1/2 hour lunch. The break at 5 hours is where the rule book states the lunch must be calculated from for pay purposes, however practise generally puts it where ever they want. Nightrate is only ever valid for hours worked between 8pm and 6am. So if a person works from 1700 - 01:30, I need the calc to show 5 hours nightrate 17:00 - 20:00 normal rate (3 hours) 2000 - 22:00 nightrate (2 hours) 22:00 - 22:30 lunch unpaid 2230 - 0130 nightrate (3 hours) Am I asking excel to do too much? "ShaneDevenshire" wrote: Hi, Suppose they work 5.25 hours what do you give them for a lunch break 1/2 hour, 1/4 hour or something else? If they work an 8.5 hour shift do you count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work? If they complete an 8 hour shift at 6PM but haven't taken lunch do you consider the lunch (6:00-6:30 PM) as night shift? -- Thanks, Shane Devenshire "Sara" wrote: Hi there, I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Calculations over a week
Hi,
If you are wondering why it took so long: There are 8 conditions Start<6AM Finish<6AM Start<6AM Finish=6AM Start6AM Finish<8PM Start<=8PM Finish8PM Start8PM Finish8PM Start<=8PM Finish Next day Start8PM Finish Next day before 6 am Start8PM Finish Next day after 6 AM So the formula is: =IF(AND(A2<6/24,B2<=6/24),B2-A2,IF(AND(A2<=6/24,B2=6/24),6/24-A2,IF(AND(A2<=20/24,B2=20/24,B2<=24/24),B2-20/24,IF(AND(A2=20/24,B2<=1,B220/24),B2-A2,IF(AND(A2<=20/24,B2<=6/24),4/24+B2,IF(AND(A2=20/24,B2<=6/24),1-A2+B2,IF(AND(A2=20/24,B2=6/24),1-A2+6/24,0))))))) And this doesn't take lunch breaks into account. The start times are in A2, the end times in B2. It may be that you define a shift not to cross the day line, in which case we could make the formula simplier. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sara" wrote: How about simplifying things? What would be the formula if we don't take the lunch break into consideration? I can then adapt the roster's to break for lunch "ShaneDevenshire" wrote: No your not asking it to do too much, but experience tells me that these benefits calculation can turn out to be a lot more complicate than initially stated. I have constructed timesheet/pay worksheets that would make your hair curl because there were so many variations to deal with. For those types of problem spreadsheet formulas were just not possible and I had to write VBA custom functions for everthing. Well I look over all of your responses and see what I can come up with. -- Thanks, Shane Devenshire "Sara" wrote: Lunch breaks are always 1/2 hour long. A shift of 5.25 hours doesn't exist - though I have seen a 5.5 hour shift. An 8.5 hour shift is 8 hours work, 1/2 hour lunch. The break at 5 hours is where the rule book states the lunch must be calculated from for pay purposes, however practise generally puts it where ever they want. Nightrate is only ever valid for hours worked between 8pm and 6am. So if a person works from 1700 - 01:30, I need the calc to show 5 hours nightrate 17:00 - 20:00 normal rate (3 hours) 2000 - 22:00 nightrate (2 hours) 22:00 - 22:30 lunch unpaid 2230 - 0130 nightrate (3 hours) Am I asking excel to do too much? "ShaneDevenshire" wrote: Hi, Suppose they work 5.25 hours what do you give them for a lunch break 1/2 hour, 1/4 hour or something else? If they work an 8.5 hour shift do you count 8.5 hours of work and 1/2 hour lunch or 8 hours work and 1/2 hour work? If they complete an 8 hour shift at 6PM but haven't taken lunch do you consider the lunch (6:00-6:30 PM) as night shift? -- Thanks, Shane Devenshire "Sara" wrote: Hi there, I have a spreadsheet to calculate shift hours with each day of the week stating start and finish times. A1 = Sun Start, B1 = Sun Finish, C1 = Mon Start, D1 = Mon Finish etc I'm trying to calculate the total number of nightrate hours for a week (to 2 decimals points) worked between 8pm & 6am. This formula also needs to subtract the lunchbreak of 1/2 hour after 5 hours work. Shifts range from 4 hours to 8.5 hours per day and not all shifts go into the nightrate period. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WEEKNUM calculations for week stating with Tuesday | Excel Worksheet Functions | |||
Rolling 12 week calculations | Excel Worksheet Functions | |||
Time difference calculations, daylight savings time, Excel | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
reverse week count calculations | Excel Worksheet Functions |