Hi, welcome to the board!
How do you get 1.25 from those times? It looks like 2 hours and ten minutes
This is a discussion on Calculate Time, but round to nearest 15 minutes within the Excel Questions forums, part of the Question Forums category; We bill services in 15 minute intervals. I am trying to calculate the time between two cells and have the ...
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.
Hi, welcome to the board!
How do you get 1.25 from those times? It looks like 2 hours and ten minutes
Office 2007/2010
=ROUND((B1-A1)*24*4,0)/4
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
Office 2007/2010
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
but doesn't show as clearly what is going onWhich of course could be written as:
=ROUND((B1-A1)*96,0)/4
I assumed that was just a typo, 1.25 is just ridiculous with those numbers.but it will return 2.25 for your example, not 1.25
Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.I assumed that was just a typo, 1.25 is just ridiculous with those numbers.
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.
Office 2007/2010
Yes, sorry. 2.25 was the desired result.
Mahalo to all of you.
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.Not necessarily. It may very well be a typo, or the op might actually want 1.25, thus my clarification question.I assumed that was just a typo, 1.25 is just ridiculous with those numbers.
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.
Office 2007/2010
Like this thread? Share it with others