Times.....

[Ampleford]

I have a spreadsheet which calculates and shows peoples attendance times. However, the "Total Time Worked" column (say Column B) shows 07:55 hrs or 08:13 hrs. I'd like to round it so that if they do (up to) three minutes into a quarter, then it rounds down, but if they do more than three minutes into a quarter, it rounds up to the next quarter hour.

eg - Employee A works 08:03 hours = Paid 8.00, Employee B works 08:04 hours = Paid 8.25


Can anyone assist me?

 

[DonkeyOte]

does this work for you ?

=HOUR(A1)+(IF(ROUND((MINUTE(A1)/15)-TRUNC(MINUTE(A1)/15),2)<0.27,MROUND(MINUTE(A1),15),CEILING(MINUTE(A1),15))/60)

assumes A1 = time worked - ie 08:03, 08:04 etc...

 

[Andrew Poulsom]

An alternative:

=ROUNDUP((A1-TIME(0,3,0))*(24*4),0)/(24*4)

 

[Ampleford]

all fixed......


Many thanks everyone