Hi - got a bit of a problem that is sort of doing my head in. :goof:
Say a business pays $20/hour when a worker works between the hours of 8am and 8pm (a day rate) - and $30/hour when they work between 8pm and 8am (night rate).
Shifts can be say 7am to 10am (1 hour @ $30, 2 hours @ $20), or 4pm until 2am (6 hours @ $20, 6 hours @ $30) or just simply 10am to 2pm (4 hours @ $20).
Anyone got a solution to how to code this into a formula that works for all of those fringe cases (i.e. overlapping day/night rates).
I have available the start and end times of the shift as timestamps, as well as the total length of the shift.
Cheers - any help greatly appreciated!