Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet and majority hours
I am not even sure if this is possible, but I figured I would ask the experts.
I would like a tool for my managers to use to assist them in paying their employees correctly, however we have shift differential rules that make it confusing. The sheet would have a Time in and Time Out column (let's say A & B) The sheet would then need to calculate the total length of the shift (Column C) Now for the tricky part... Based on the length of the shift - a certain fixed number determines the majority of the shift - if the shift is at least 6 hours, but less than 8, then 4 hours If at least 8 hours, but less than 10, then 5 hours Then with that "majority number in mind", they would have to work that many hours after 1500 (3 o'clock) - so we know to may shift differential for the entire shift For example 0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the employee did not work 5 hours from 1500, then the entire pay will be 1st shift 1100-1800 - Total time is 7 hours ( so 4 hours determines the majority). Since the employee worked 4 hours in 2nd shift (after 1500), then the entire shift will be paid 2nd Like I said, this may not even be possible, but I figured it would be worth a shot! Thanks ahead of time |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet and majority hours
What if less than 6 or more than 10?
=if(end - start <8,if(end - 4/24 = 15/24,2nd shift pay,1st shift pay),if(end - 5/24 = 15/24, 2ndshift pay, 1st shift pay) should get the below criteria. Add more if other hour for shift... "Amanda" wrote: I am not even sure if this is possible, but I figured I would ask the experts. I would like a tool for my managers to use to assist them in paying their employees correctly, however we have shift differential rules that make it confusing. The sheet would have a Time in and Time Out column (let's say A & B) The sheet would then need to calculate the total length of the shift (Column C) Now for the tricky part... Based on the length of the shift - a certain fixed number determines the majority of the shift - if the shift is at least 6 hours, but less than 8, then 4 hours If at least 8 hours, but less than 10, then 5 hours Then with that "majority number in mind", they would have to work that many hours after 1500 (3 o'clock) - so we know to may shift differential for the entire shift For example 0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the employee did not work 5 hours from 1500, then the entire pay will be 1st shift 1100-1800 - Total time is 7 hours ( so 4 hours determines the majority). Since the employee worked 4 hours in 2nd shift (after 1500), then the entire shift will be paid 2nd Like I said, this may not even be possible, but I figured it would be worth a shot! Thanks ahead of time |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet and majority hours
Set it up like so:
A2 = 3:00 PM (time 2nd shift starts) (start) A4: 11:00 AM (end) B4: 8:00 PM (hours, format as General) C4: =(B4-A4)*24 (majority) D4: IF(AND(C4=6,C4<8),4, IF(AND(C4=8,C4<10),5,0)) (hours into 2nd shift) E4: =(B4-$A$2)*24 (majority selection 1st or 2nd) F4: =IF(E4=D4,"2nd","1st") Now, that was just to show you all the logic. In G4, we can merge all those mini-formulas into one megaformula, hard to read, but does that work in a single cell: G4: =IF((B4-$A$2)*24=IF(AND((B4-A4)*24=6,(B4-A4)*24<8), 4, IF(AND((B4-A4)*24=8,(B4-A4)*24<10), 5, 0)), "2nd", "1st") Now that you have the shift choice, do your math on the pay rate. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Amanda" wrote: I am not even sure if this is possible, but I figured I would ask the experts. I would like a tool for my managers to use to assist them in paying their employees correctly, however we have shift differential rules that make it confusing. The sheet would have a Time in and Time Out column (let's say A & B) The sheet would then need to calculate the total length of the shift (Column C) Now for the tricky part... Based on the length of the shift - a certain fixed number determines the majority of the shift - if the shift is at least 6 hours, but less than 8, then 4 hours If at least 8 hours, but less than 10, then 5 hours Then with that "majority number in mind", they would have to work that many hours after 1500 (3 o'clock) - so we know to may shift differential for the entire shift For example 0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the employee did not work 5 hours from 1500, then the entire pay will be 1st shift 1100-1800 - Total time is 7 hours ( so 4 hours determines the majority). Since the employee worked 4 hours in 2nd shift (after 1500), then the entire shift will be paid 2nd Like I said, this may not even be possible, but I figured it would be worth a shot! Thanks ahead of time |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet and majority hours
Wow...that is great...I like that you explained it all out to me as well....I
didn't think it would work...but since you got that far....can I try to throw in one more twist? We actually have 3 shifts....that the majority rules apply.... 2nd shift is 3:00 pm to Midnight 3rd shift is 11:00 pm to 08:00 am Is that possible to add? Also, is there a way to input time in military time, without having to write the AM and PM....like rather than 2:00 pm, I can input 1400, but it knows that is time? Thanks a ton!!! "JBeaucaire" wrote: Set it up like so: A2 = 3:00 PM (time 2nd shift starts) (start) A4: 11:00 AM (end) B4: 8:00 PM (hours, format as General) C4: =(B4-A4)*24 (majority) D4: IF(AND(C4=6,C4<8),4, IF(AND(C4=8,C4<10),5,0)) (hours into 2nd shift) E4: =(B4-$A$2)*24 (majority selection 1st or 2nd) F4: =IF(E4=D4,"2nd","1st") Now, that was just to show you all the logic. In G4, we can merge all those mini-formulas into one megaformula, hard to read, but does that work in a single cell: G4: =IF((B4-$A$2)*24=IF(AND((B4-A4)*24=6,(B4-A4)*24<8), 4, IF(AND((B4-A4)*24=8,(B4-A4)*24<10), 5, 0)), "2nd", "1st") Now that you have the shift choice, do your math on the pay rate. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Amanda" wrote: I am not even sure if this is possible, but I figured I would ask the experts. I would like a tool for my managers to use to assist them in paying their employees correctly, however we have shift differential rules that make it confusing. The sheet would have a Time in and Time Out column (let's say A & B) The sheet would then need to calculate the total length of the shift (Column C) Now for the tricky part... Based on the length of the shift - a certain fixed number determines the majority of the shift - if the shift is at least 6 hours, but less than 8, then 4 hours If at least 8 hours, but less than 10, then 5 hours Then with that "majority number in mind", they would have to work that many hours after 1500 (3 o'clock) - so we know to may shift differential for the entire shift For example 0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the employee did not work 5 hours from 1500, then the entire pay will be 1st shift 1100-1800 - Total time is 7 hours ( so 4 hours determines the majority). Since the employee worked 4 hours in 2nd shift (after 1500), then the entire shift will be paid 2nd Like I said, this may not even be possible, but I figured it would be worth a shot! Thanks ahead of time |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
time sheet and majority hours
Sorry, I didn't notice the reply. Honestly, I'm drawing a blank for the
moment on making this s a 3-way logic-test. But the Military time this is easy. Click on some blank cell and just enter this: 20:00 excel now knows that's a military format time, highlight the cell and click on the Format Painter, then highlight all your existing Time Cell entries and they will all display in military format. Excel keeps time with hidden decimal values, so you changing the DISPLAY parameters has no effect on the formulas used to manipulate the time values. Anytime you want to enter in military time, just type in the semicolon, too. Enter 21: and press ENTER and it will register as time. For 9:30 PM, just enter 21:3 Hope that helps. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Amanda" wrote: Wow...that is great...I like that you explained it all out to me as well....I didn't think it would work...but since you got that far....can I try to throw in one more twist? We actually have 3 shifts....that the majority rules apply.... 2nd shift is 3:00 pm to Midnight 3rd shift is 11:00 pm to 08:00 am Is that possible to add? Also, is there a way to input time in military time, without having to write the AM and PM....like rather than 2:00 pm, I can input 1400, but it knows that is time? Thanks a ton!!! "JBeaucaire" wrote: Set it up like so: A2 = 3:00 PM (time 2nd shift starts) (start) A4: 11:00 AM (end) B4: 8:00 PM (hours, format as General) C4: =(B4-A4)*24 (majority) D4: IF(AND(C4=6,C4<8),4, IF(AND(C4=8,C4<10),5,0)) (hours into 2nd shift) E4: =(B4-$A$2)*24 (majority selection 1st or 2nd) F4: =IF(E4=D4,"2nd","1st") Now, that was just to show you all the logic. In G4, we can merge all those mini-formulas into one megaformula, hard to read, but does that work in a single cell: G4: =IF((B4-$A$2)*24=IF(AND((B4-A4)*24=6,(B4-A4)*24<8), 4, IF(AND((B4-A4)*24=8,(B4-A4)*24<10), 5, 0)), "2nd", "1st") Now that you have the shift choice, do your math on the pay rate. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Amanda" wrote: I am not even sure if this is possible, but I figured I would ask the experts. I would like a tool for my managers to use to assist them in paying their employees correctly, however we have shift differential rules that make it confusing. The sheet would have a Time in and Time Out column (let's say A & B) The sheet would then need to calculate the total length of the shift (Column C) Now for the tricky part... Based on the length of the shift - a certain fixed number determines the majority of the shift - if the shift is at least 6 hours, but less than 8, then 4 hours If at least 8 hours, but less than 10, then 5 hours Then with that "majority number in mind", they would have to work that many hours after 1500 (3 o'clock) - so we know to may shift differential for the entire shift For example 0900-1800 - Total time is 9 hours (so 5 determines the majority). Since the employee did not work 5 hours from 1500, then the entire pay will be 1st shift 1100-1800 - Total time is 7 hours ( so 4 hours determines the majority). Since the employee worked 4 hours in 2nd shift (after 1500), then the entire shift will be paid 2nd Like I said, this may not even be possible, but I figured it would be worth a shot! Thanks ahead of time |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for hours on time sheet | Excel Discussion (Misc queries) | |||
How do I time Hours & mins in excel - Time sheet | Excel Discussion (Misc queries) | |||
Time Sheet to show hours owed | Excel Worksheet Functions | |||
Is there a time sheet template that calculates hours? | Excel Discussion (Misc queries) | |||
time-sheet record with over 24 hours | New Users to Excel |