Rounding time up to the nearest quarter hour

[longbeachdrive]

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

 

[AhoyNC]

Try:

=CEILING(B2-A2,25/1440)

 

[longbeachdrive]

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]

T202109a.xlsm
	A	B	C	D	E
1	1	Start Time	End Time	Total Hours Worked	Time
2	2	4:00 PM	4:15 PM	00:15	00:15:00
3	3	2:00 PM	2:15 PM	00:15	00:15:00
4	4	2:08 PM	2:23 PM	00:15	00:15:00
5		14:00	14:55	00:55	01:00:00
6		14:00	15:32	01:32	01:45:00
7		12:00	12:26	00:26	00:30:00
8
1c

Cell Formulas
Range	Formula
D2:D7	D2	=C2-B2
E2:E7	E2	=CEILING(ROUND(C2-B2,4),1/96)

 

[Dave Patton]

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
	A	B	C	D
1
2	12:00	14:04	2.25
3
End

Cell Formulas
Range	Formula
C2	C2	=CEILING.PRECISE((B2-A2)*24,0.25)

 

[Alex Blakenburg]

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
	A	B	C	D	E	F	G
1	1	Start Time	End Time	Total Hours Worked	Quarter Hours Worked (rounded up)	Alternative formula	Difference C2-B2
2	2	4:00 PM	4:15 PM	0:15	0:30	0:15	0.01041666666666670
3	3	2:00 PM	2:15 PM	0:15	0:15	0:15	0.01041666666666660
4	4	2:08 PM	2:23 PM	0:15	0:15	0:15	0.01041666666666660
5
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=CEILING.PRECISE(C2-B2-TIMEVALUE("00:00:00.1"),"0:15")
G2:G4	G2	=C2-B2

 

[Phuoc]

Another way:

Book1
	A	B	C	D
1	Start Time	End Time	Total Hours Worked	Time
2	9:00	16:45	7:45	7:45
3	9:00	17:00	8:00	8:00
4	9:00	17:15	8:15	8:15
5	9:00	17:30	8:30	8:30
6	9:00	17:45	8:45	8:45
7	9:00	18:00	9:00	9:00
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=B2-A2
D2:D7	D2	=CEILING(ROUND((B2-A2)*1440,0),15)/1440

 

[longbeachdrive]

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]

Glad we were able to help. Thanks for the feedback.