Time Sheet calculation help EXCEL

[Bronte1226]

We have been using the following formula in our time sheets to calculate each day.

=((HOUR(I22-F22))+(MINUTE(I22-F22))/60)+((HOUR(O22-L22))+(MINUTE(O22-L22))/60)+((HOUR(U22-R22))+(MINUTE(U22-R22))/60)

Up to this point we have rounded up or down to the nearest quarter hour our "clock in" and out times.

However, with our new policy changes we are having to input the actual time, down to the minute, in each cell and have been requested to update the formula to round up or down in one lump at the end.

This time however, we are to round up or down to the nearest quarter, but in accordance to the following criteria: 7 or less, round down, 8 or more round up. So if one was to clock in at 8:06, it would round down to 8:00. But if clocked in at 8:08, round up to 8:15, etc. The actual time cells can't be changed, just the final "time worked" cell.

My question...HOW does one do this? Is this an If, then type of clause? I am lost.

 

[SkipVought]

Hi,

I would strongly advise to work with REAL TIME VALUES, not Hours and Minutes in separate cells. You'll have a difficult time at the hour boundaries.

Using REAL TIME VALUES, and assuming that your total time is in A1,
[tt]
A1: =INT((A1+TIME(0,7,0))/TIME(0,15,0))*TIME(0,15,0)
[/tt]
Then, if you want hours and minutes, just use the Hour & Minute functions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}