Hi all,
Forgive me if this has been covered but I need to create a formula for calculating employees clocking in and out, I have the formula to ROUNDDOWN to the nearest quarter hour =FLOOR(CELL,"0:15") and ROUNDUP = CEILING(CELL,0:15) but my company pay to the quarter hour and allow 3 minuets late grace in clocking on in the morning, so I need to build this 3 minuets grace in to my calculation.
E.G.
Monday John clock in at 07:58 takes 1 hour for lunch and clocks of at 17:00, he would be payed from 08:00 to 17:00 - 1 hour Lunch = 8 hours.
Tuesday he clocks in at 08:04 takes 1 hour for lunch and clocks of at 17:00, he would be paid from 08:15 to 17:00 - 1 hour Lunch = 7.75 hours. (he went over the start time 3 minuet grace rule so looses 15 minuets).
I did think I could use IF statements but I then realized needed a formula to ignore the hour and only work on the minuets as we have staff that start at different times of the day.
The time sheet works with hh:mm (hour & minuet) format with a decimal total hours worked per day.
Thanks in advance,
Birdy25.
Bookmarks