Rounding time up to the nearest quarter hour

longbeachdrive

New Member
Joined
Sep 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 BCD
1Start TimeEnd TimeTotal Hours WorkedQuarter Hours Worked (rounded up)
24:00 PM4:15 PM0:150:30
32:00 PM2:15 PM0:150:15
42:08 PM2:23 PM0:150:15
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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