Hi,
I have a problem I have been trying to find a solution to for a while now. We have people that work shifts. Bonuses can be received for each shift depending on time and day.
---**disclaimer** this is not the actual bonus system, since we don't even use dollars in my country but it's just to get the point across.---
For each hour worked between 12am and 7:59am you get $2.
For each hour worked between 08am and 3:59pm there is no bonus, so $0,
for each hour worked between 4pm to 11:59pm there is a bonus of $1.
On top of this the system is a little different depending on the day of the week, ie you get higher bonus on weekends (sat-sun) but let's keep that out of it for now.
So, if the shift is, from 11pm to 8am the worker should get $1 for 11pm to 12am, $2 for every hour worked between 12am to 7am (so 7*2) and no bonus for the last hour from 7am to 8am. In total $15.
Then we have various shifts, and over 700 employees so calculating each shift per person by hand is absolute madness. How do I get excel to calculate the bonus for each person, each shift and return a value. I would like to be able to use the shift schedule made by the shift manager to see how much bonus each worker on the schedule should get. Shift schedule includes dates and start and end time of each shift per person.
I hope the problem is clear, if not, please ask for clarification or more details
Thanks in advance!
I have a problem I have been trying to find a solution to for a while now. We have people that work shifts. Bonuses can be received for each shift depending on time and day.
---**disclaimer** this is not the actual bonus system, since we don't even use dollars in my country but it's just to get the point across.---
For each hour worked between 12am and 7:59am you get $2.
For each hour worked between 08am and 3:59pm there is no bonus, so $0,
for each hour worked between 4pm to 11:59pm there is a bonus of $1.
On top of this the system is a little different depending on the day of the week, ie you get higher bonus on weekends (sat-sun) but let's keep that out of it for now.
So, if the shift is, from 11pm to 8am the worker should get $1 for 11pm to 12am, $2 for every hour worked between 12am to 7am (so 7*2) and no bonus for the last hour from 7am to 8am. In total $15.
Then we have various shifts, and over 700 employees so calculating each shift per person by hand is absolute madness. How do I get excel to calculate the bonus for each person, each shift and return a value. I would like to be able to use the shift schedule made by the shift manager to see how much bonus each worker on the schedule should get. Shift schedule includes dates and start and end time of each shift per person.
I hope the problem is clear, if not, please ask for clarification or more details
Thanks in advance!
