Hi - I am trying to write a formula to calculate hours worked daily on a time card. I have that formula for the regular 8 hours ex. "=ROUND(IF((((S5-R5)+(U5-T5))*24)>8,8,((S5-R5)+(U5-T5))*24),0)" but am getting stuck on formula to round the average of time worked in the overtime column to the nearest quarter hour according to our time clock rules (ex 8 min beyond hour would be 15 min and 7 mins and less would round down to 0.
This is the formula I am using "=IF(((S5-R5)+(U5-T5))*24>8,((S5-R5)+(U5-T5))*24-8,0)" but it returns the value of 0.07 because on that line the person actually had 7 min worked by clocking in few minutes before and after lunch/work start & end times. I am building a template for our new time clocks that download into excel with only the time punches. I want the report to auto calculate hours and overtime hours worked. (file attached - am only working with the first employee at this point)
As you can see in the file on Wednesday it is showing -9 when they actually worked 15.25 hours that day and should be 8 & 7.25
Bookmarks