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

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
Joined
Oct 10, 2011
Messages
4,929
Office Version
  1. 365
Platform
  1. Windows
Try:
=CEILING(B2-A2,25/1440)
 

longbeachdrive

New Member
Joined
Sep 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
5,186
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

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

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
579
Office Version
  1. 2016

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
Joined
Sep 1, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top