# 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

Thank you for your help.

 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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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

ADVERTISEMENT

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

##### Well-known Member
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

ADVERTISEMENT

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

##### Well-known Member
Glad we were able to help. Thanks for the feedback.

### Similar threads

Replies
1
Views
466
Replies
3
Views
51
Replies
0
Views
170
Replies
5
Views
149
Replies
5
Views
53

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.

Threads
1,151,826
Messages
5,766,669
Members
425,367
Latest member
Boboka

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

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