Calculate Time, but round to nearest 15 minutes

chadmiller

New Member
Joined
Jan 1, 2007
Messages
23
We bill services in 15 minute intervals. I am trying to calculate the time between two cells and have the answer in hours, with two decimals, but in .25 minute increments.

Desired result:
1/1/07 12:00 in A1
1/1/07 14:10 in B1
1.25 in C1

0-7 Minutes - round down to 0.00
8-22 Minutes - round to 0.25
23-37 Minutes - round to 0.50
38-52 Minutes - round to 0.75
52-60 Minutes - round to 1.00

Because this is billable time, the number is never negative, so I don't need the formula to verify the first date/time cell is less than the second.

Any suggestions would be greatly appreciated.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi, welcome to the board!

How do you get 1.25 from those times? It looks like 2 hours and ten minutes
 
Upvote 0
Which of course could be written as:
=ROUND((B1-A1)*96,0)/4

but it will return 2.25 for your example, not 1.25
 
Upvote 0
If you don't mind using Analysis ToolPak functions you could also use MROUND, i.e

=MROUND((B1-A1)*24,0.25)

or

=MROUND(B1-A1,"0:15")*24
 
Upvote 0
Which of course could be written as:
=ROUND((B1-A1)*96,0)/4

but doesn't show as clearly what is going on

but it will return 2.25 for your example, not 1.25

I assumed that was just a typo, 1.25 is just ridiculous with those numbers.
 
Upvote 0
I assumed that was just a typo, 1.25 is just ridiculous with those numbers.

Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.

Here's what I've come up with as a possible scenario for wanting 1.25

Say for example, a service call costs $75 and includes the first hour of service. Anything else after that is billed at a different rate, in this case you would not want to count the first hour, but everything after that.
 
Upvote 0
I assumed that was just a typo, 1.25 is just ridiculous with those numbers.

Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.

IYO. There was also a subject matter which was quite clear. All in, there seemed insufficient cause to pick holes in someone else's suggestion, disingenuous at the mimimum.
 
Upvote 0
I wasn't picking holes in someone elses suggestion, I was just saying the op might want 1.25 like he asked for, and since that was what he asked for maybe there was a reason he wanted that.

BTW, my suggestion was the same as yours, all I said was you didn't have to type 24*4, you could just type 96.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top