Hello all,
I am new here, I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?
E10 = 19:00 or start of night time hours
B3 = start time
C3 = end time
D3 = day hours workeds =24*IF(E10<B3, 0, IF(C3<E10, C3-B3, E10-B3))
D4= night hours worked =24*IF(C3<E10, 0, IF(E10<B3, C3-B3, C3-E10))
These formulas make it easy for me to add on the extra pay after 7pm as there is a day hour cell and a night hour cell
Also, my other problem is there is another shift called CRO (Puts me in another department) which is paid at a diff rate, (and after 7pm it will increase as the normal shifts do) but it does not fall on any particular day and can be any where from 7am to 11pm, so in my weekly roster sometimes one or two shifts will be CRO shifts. What can I do to be able to use the formulas above and switch to the diff(CRO) rate. This shift often will pass 7PM into the night hours but won't ever go past midnight.
I have attached an example sheet
Many Thanks in advance
Bookmarks