Attendance Sheet: Formula Help needed.

[viperzero]

Good day, I would like to ask for your help in getting my formula right.

I made a formula that computes my night differential by hours/day which starts at 10pm upto 7am. While the computation works, ie: a shift starting @ 3pm - 11pm has 8 hours work time including a 1 hour as night differential (still counts as 8hours total), 11pm-7am = 8hours with 7ND. However, when I change time, the ND results are not what I expected, ie: 3pm-9pm results in 6 hours ND, 6am-10am results in 4 hours ND. Below is the formula. Can you please tell me what is wrong with it?

=IF((HOUR(D6)-HOUR(C6))+((MINUTE(D6)-MINUTE(C6))/60)<0,IF(AND(HOUR(D6)>6, HOUR(D6)<14), E6, IF((HOUR(C6)<22),((HOUR(C6)-22)+E6),"0.00")),IF((HOUR(D6)<22),E6,(HOUR(D6)-22)))

C6 = start time
D6 = end time
E6 = No. of Hours computation.


Also I would like your help in making a formula for:

1> Cut-off date = gives the Salary date based on last date on the cut-off.
2> Succeeding Dates after End of Month give a null(blank) cell.
3> Something that keeps the previous amount of a cell even if it was changed.

Thank you in advance.

[rcm]

Please Login or Register  to view this content.