Rounding Up By 15-Min Intervals

[Patron Saint of Chimichangas]

Head ... ready to ... explode ...

Okay, my company bills clients in 15-minute increments. (If we work 8 minutes on the project, the customer gets billed for 15 minutes. If we work 17 minutes on the project, the customer gets billed for 30 minutes, and so on.)

I've created a Duration field that calculates (EndTime - StartTime) and tells me how long we *actually* worked on the project.

But the Cost field is set to calculate (Rate*Duration), which gives me a total billing amount that isn't right.

I've been pounding my head all day trying to figure out how to calculate this, and am just about ready to have a meltdown!

I started with this:

((( EndTime - StartTime)/3600)*4)*(Rate/4)

But I quickly discovered this only works if the difference of (EndTime - StartTime) works out to an increment of 900 seconds. Therefore, I need to always round (EndTime - StartTime) up to the next-highest 900-second increment.

Did anyone follow that? Or is there an easier way to do this that I'm not aware of?

As usual, thanks in advance for any advice.

Edited May 10, 2006 by Guest

[comment]

Try:

Ceiling ( ( EndTime - StartTime ) / 900 ) * 900

The result here is Time.

Edited May 10, 2006 by Guest

[John Mark Osborne]

Div(Time; 900) * 900 + Case(Div(Time; 900) <> Time/900; 900)

Take the integer value of the time divided by 900. That's the number of seconds in a 15 minute increment (since time is tracked in seconds). This will leave with the number of whole 15 minute increments which is multiplied by 900 seconds to turn it back into time. If the time was already an exact multiple of 15 minutes, don't add 900 additional seconds since it is already correct.

[Patron Saint of Chimichangas]

Wow -- thanks for the quick response, comment.

So, what would that bit look like in context? Would it be this?

(Ceiling ( ( EndTime - StartTime )/900 )*900 ) * ( Rate/4 )

[Patron Saint of Chimichangas]

Thanks, John. I'll try both of these solutions.

[comment]

It depends on what the rate actually is. If it is hourly, then the billed amount is:

Rate / 3600 * Ceiling ( ( EndTime - StartTime ) / 900 ) * 900

or simply:

Rate / 4 * Ceiling ( ( EndTime - StartTime ) / 900 )