# Rounding time up to the nearest quarter hour

#### longbeachdrive

##### New Member
I am tracking time for short projects and having trouble with rounding to the nearest quarter hour.

I am not sure why, but sometimes projects that are exactly 15 minutes round up to the next quarter hour and sometimes it remains at 15. I would like projects of 15 minutes or less to bill at 15 minutes, not 30.

Columns A and B are formatted as h:mm AM/PM
The formula in Column C is =B2-A2, and Column C is formatted as h:mm.
The formula in Column D is =CEILING(B2-A2,1/96), and Column D is formatted as h:mm

 A B C D 1 Start Time End Time Total Hours Worked Quarter Hours Worked (rounded up) 2 4:00 PM 4:15 PM 0:15 0:30 3 2:00 PM 2:15 PM 0:15 0:15 4 2:08 PM 2:23 PM 0:15 0:15

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### AhoyNC

##### Well-known Member
Try:
 =CEILING(B2-A2,25/1440)

#### longbeachdrive

##### New Member
I tried your solution but it returned 15 minutes for anything up to 25 min of work, and then rounded up to 50 minutes after 26 minutes of work.

#### Dave Patton

##### Well-known Member

Another that you can try depending on your requirements.
Check Excel's help for information. N.B. I have never used this function but it may be good reading.

3D Sum.xlsm
ABCD
1
212:0014:042.25
3
End
Cell Formulas
RangeFormula
C2C2=CEILING.PRECISE((B2-A2)*24,0.25)

#### Alex Blakenburg

##### MrExcel MVP
sometimes projects that are exactly 15 minutes round up to the next quarter hour and sometimes it remains at 15

The error you are getting is based on the fact that Excel stores time a a fraction of a day and the fractions tend to finish up in a recurring decimal.
See if this fixes it.

20210902 Time Rounded to 15 min.xlsx
ABCDEFG
11Start TimeEnd TimeTotal Hours WorkedQuarter Hours Worked (rounded up)Alternative formulaDifference C2-B2
224:00 PM4:15 PM0:150:300:150.01041666666666670
332:00 PM2:15 PM0:150:150:150.01041666666666660
442:08 PM2:23 PM0:150:150:150.01041666666666660
5
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=CEILING.PRECISE(C2-B2-TIMEVALUE("00:00:00.1"),"0:15")
G2:G4G2=C2-B2

Last edited:

#### Phuoc

##### Well-known Member

Another way:

Book1
ABCD
1Start TimeEnd TimeTotal Hours WorkedTime
29:0016:457:457:45
39:0017:008:008:00
49:0017:158:158:15
59:0017:308:308:30
69:0017:458:458:45
79:0018:009:009:00
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=B2-A2
D2:D7D2=CEILING(ROUND((B2-A2)*1440,0),15)/1440

#### longbeachdrive

##### New Member
The error you are getting is based on the fact that Excel stores time a a fraction of a day and the fractions tend to finish up in a recurring decimal.
This worked for me. I haven't had a chance to experiment with the other solutions in the thread yet, but this one seems to do exactly what I needed. Thank you for explaining why it wasn't working before, and thank you all for sharing your knowledge and time!

#### Alex Blakenburg

##### MrExcel MVP
Glad we were able to help. Thanks for the feedback.

Replies
6
Views
480
Replies
2
Views
104
Replies
8
Views
275
Replies
1
Views
27
Replies
7
Views
84

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,459
Messages
5,831,760
Members
430,088
Latest member
meagerd

### 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.

### Which adblocker are you using?

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

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