excel - rounding time to the nearest 15 minutes

excel vba worksheet-function
Alex Gordon picture Alex Gordon · Jun 1, 2011 · Viewed 8.8k times · Source

I have a formula that computes the difference of several TIMES and multiplies it by 24 to get a decimal value for the total:

D10=(C9-D9)*24

I would like to do special rounding like this:

if D9 is 1pm and C9 is 2:08 pm, i would like D10 to be 1.25

another words

1:53 until 2:07 would be counted as 2
2:08 until 2:22 would be 2:15
2:23 through 2:37 would be 2:30
etc

Does anyone know how to do such special rounding?

as doug put it:

i need the accrued time from 1 PM to 2:08 PM should round up to 1.25 hours whereas 1 PM to 2 PM would round down to 1 hour, in other words to the nearest quarter hour.

Answer

Lance Roberts picture Lance Roberts · Jun 1, 2011

Here is the formula that will partition out the difference as you wanted:

=(TRUNC((D9+VALUE("00:07"))*96)-TRUNC((C9+VALUE("00:07"))*96))*VALUE("00:15")*24

Related questions

Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
How to show current user name in a cell?

In most of the online resource I can find usually show me how to retrieve this information in VBA. Is there any direct way to get this information in a cell? For example as simple as =ENVIRON('User') (which did …

Read More
WorksheetFunction.CountA - not working post upgrade to Office 2010

The following piece of code works in Excel prior to 2010: myRange = Range("A:A") NumRows = Application.CountA(myRange) There are 38 cells containing text/values in column A. When the code is run in Excel 2007, NumRows correctly evaluates to 38, however, it (…

Read More