# Rounding Up By 15-Min Intervals

This topic is 5687 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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?

Edited by Guest
##### Share on other sites

Try:

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

The result here is Time.

Edited by Guest
##### Share on other sites

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.

##### Share on other sites

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 )

##### Share on other sites

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

##### Share on other sites

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 )

##### Share on other sites

This topic is 5687 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account