Calculating quarterly hours with rounding.

[justinwh]

I'm trying to set up a check-solution system when my staff enter in the hourly attendance of our clients.

The rounding system is a bit weird, so let me briefly explain it as best I can.

Our regular hours are from 8:00am - 2:00pm.

We charge per 15 minutes.

A minute before 8:00am would be charged for 15 minutes.
A minute over 2:00pm would be charged for 15 minutes.
This has to do with staff:client ratio.

Policy for calculating hours is that we roundtowards 8:00am(if the time is after 8:00) and 2:00pm(if the time is before 2:00), and we round away from 8:00am(if the time is before 8:00) and 2:00pm(if the time is after 2:00) in quarterly increments. This is how staff coverage is compensated.

Here are some examples of what I'm talking about:

In/out/total
(7:43/1:55/6.25)
(7:30/1:46/6.5)
(7:31/2:01/6.5)

Rounded, that would be
(7:45/2:00/6.25)
(7:30/2:00/6.5)
(7:45/2:15/6.5)

I want a simple A1-C2 range.
A1-C1 = "Time in", "Time out", "Total".
A2-C2 = time in input, time out input, formula to calculate total hours.

How hard would this be? I understand the rounding may be a bit unconventional (which is partly why I couldn't use a past forum post to answer my question). Hope it makes sense. I've re-read it a couple times and I'm fairly certain I haven't typo'd. Let me know if I can clarify anything.

EDIT: my C2 formula currently looks like =(MROUND(B2-A2,15/1440)*24). It doesn't do exactly what I want it to, regarding the rounding issue. It only rounds to the nearest quarter, which isn't always the best method. It works if I manually round the A2 and B2 cells, which is not a problem, but I just wanna see how automated I can get this sheet so all my staff have to do is enter in data verbatim.

 

[RonB1111]

Please clarify: "... we round away from 8:00am(if the time is before 8:00)... but your examples of 7:43 and 7:31 round to 7:45 which is "towards" 8:00, not away from it.

Also, is rounding done on both the in and out times or just the difference. eg: Does "start 7:55" and "end 8:10" equal 15 or 30 total minutes.

Maybe a few more examples with expected results would help as well.

 

[justinwh]

Please clarify: "... we round away from 8:00am(if the time is before 8:00)... but your examples of 7:43 and 7:31 round to 7:45 which is "towards" 8:00, not away from it.

Also, is rounding done on both the in and out times or just the difference. eg: Does "start 7:55" and "end 8:10" equal 15 or 30 total minutes.

Maybe a few more examples with expected results would help as well.

Hmmm. I knew I was going to trip up somewhere. Putting more thought into it, the correct times are updated below. I have no idea what I was trying to say up there, and we can probably chalk it up to being way too early in the morning for me

In/out/total
(7:43/1:55/6.5)
(7:30/1:46/6.5)
(7:31/2:01/6.75)

Rounded, that would be
(7:30/2:00/6.5)
(7:30/2:00/6.5)
(7:30/2:15/6.75)


Rounding is done only on the in/out. 7:55(7:45) to 8:10(8:15) = 0:30

 

[RonB1111]

Try:

=(CEILING(B2,15/1440)-FLOOR(A2,15/1440))*24

 

[justinwh]

Works as expected now. Thank you!

 

[RonB1111]

You're welcome!