Calculation of inconvenient working hours

Would need some help setting up a formula in Excel for calculation of inconvenient working hours.
I would be very happy if Step 1 below could be solved. We have another complication which I describe in Step 2. If that one could be solved too, that would be fantastic. Is it possible to solve both within the same formulas?

Step1

I work in a company where the employees will have additions to the salary for working inconvenient hours. We have 3 different categories of additions to the salary. All other hours (6 AM - 6 PM on weekdays) come at standard rates.
Cat A: Weekday nights between 6 PM - 6 AM

Cat B: Daytime during weekends: 6 AM - 6 PM

Cat C: Nights during weekend 6 PM - 6 AM

How can I control for that the formula calculates the inconvenient hours correctly?

Example: A person is working 3 PM - 1 AM on a Monday, i.e there should be a formula calculating additional salary according to Cat A from 6 PM until 1 AM. 3 PM - 6 PM comes at standard rates.


Step 2

We have a rule stating that "If the employee works at night before a weekday, and the shift has begun before 12 PM there should be additional salary until the end of the shift, however at longest until 8 AM the morning after.
So in fact, these are hours that we would need to take into account for calculation everything correctly:

Cat A: Weekday nights between 6 PM - 6 AM (or until 8 AM if the shift started before 12 PM)

Cat B: Daytime during weekends: 6 AM - 6 PM

Cat C: Nights during weekend 6 PM - 6 AM (or until 8 AM Sunday night (ending Monday morning) if the shift started before 12 PM)

Example: A person is working 10 PM - 9 AM on a Monday, i.e there should be a formula calculating additional salary according to Cat A from 10 PM until 8 AM. 8 AM-9 AM should come at standard rate.


Thank you very much in advance for your help!

Hi,

Thank you for your reply. I am sorry for my late response.


1 am to 5 am (in fact until 6 am, and until 8 am if the shift began before 12 pm) on Monday morning is classified as weekend (Cat C). Hence the whole shift from 9pm Sunday - 5 am Monday is cat C.


1 am to 5 am (in fact until 6 am) Friday night/Saturday morning is also weekend, Cat C.


Just let me know if you have further questions.

Hi,

Thank you.

I have now attached a file with examples and expected output.

Kind regards

Dear JeteMc, Sorry for my very late reply, and thank you so much for taking your time to help me. It helps a lot.
However, the last shift (26th of August in this example) is not completely right. The extended weekend night rate should only be valid until 8 AM. After 8 AM there will be a standard rate again.
How would I add a time restriction to these formulas saying that after 8 AM there will always be a day rate again?

i cant seem to make this work for my requirements.

Cat A would be Night shift - 20:00 - 06:00
Cat B would be Day shift - 06:00 - 20:00
Cat C would Be Sat shift - Midnight Friday - Midnight Saturday
Cat D would Be Sun Shift - Midnight Saturday - Midnight sunday

i have the Cat A calculating Nights correctly but i can't seem to figure out how to make the rest work?
any suggestions

Thank you for taking your time. I actually think we have a solution now, thanks to your help!