Hi,
im trying to create a table that will automatically work out what I need to pay my staff. We pay day rate to some people when they work between the hours of 4am and 3:59pm, and pay nights when they work 4pm - 3:59am. Some get 6am - 5:59pm and vice versa so I will need to be able to change this. So, if someone works 10am - 6pm (and they are paid days 4am-3:59pm and nights 4pm-3:59am) then they would receive 6 hours at days and 2 hours at nights. Now, to make this more complicated there is also overtime, so after 8 hours the hours would need to go into the overtime column. To give you another example a different person (who gets paid 6am-5:59pm days and 6pm-5:59am nights) works 4am until 8pm. They would receive 2 hours at nights, 6 hours at days, 4 hours at days overtime and 4 hours at nights overtime. I hope you follow.
So, what I am trying to get here is a table where I can input what hours they are paid 'days' for, what hours they are paid 'nights' for and their start and finish time and from then on excel does its thing and tells me what I need to pay.
i have been trying to figure this out for months using my basic excel skills so any help would be really appreciated. I can either do it through formulas or VBA as I have a basic knowledge of both and am equally comfortable.
Thanks for reading!
im trying to create a table that will automatically work out what I need to pay my staff. We pay day rate to some people when they work between the hours of 4am and 3:59pm, and pay nights when they work 4pm - 3:59am. Some get 6am - 5:59pm and vice versa so I will need to be able to change this. So, if someone works 10am - 6pm (and they are paid days 4am-3:59pm and nights 4pm-3:59am) then they would receive 6 hours at days and 2 hours at nights. Now, to make this more complicated there is also overtime, so after 8 hours the hours would need to go into the overtime column. To give you another example a different person (who gets paid 6am-5:59pm days and 6pm-5:59am nights) works 4am until 8pm. They would receive 2 hours at nights, 6 hours at days, 4 hours at days overtime and 4 hours at nights overtime. I hope you follow.
So, what I am trying to get here is a table where I can input what hours they are paid 'days' for, what hours they are paid 'nights' for and their start and finish time and from then on excel does its thing and tells me what I need to pay.
i have been trying to figure this out for months using my basic excel skills so any help would be really appreciated. I can either do it through formulas or VBA as I have a basic knowledge of both and am equally comfortable.
Thanks for reading!
