Floor and Ceiling problem

[brettr87]

Looking for help adjusting my formula in column D (Ceiling) so that instances like D5 would read as 15:15:00 as opposed to 15:00:00.

Help is greatly appreciated.

Excel Workbook
	A	B	C	D
1		Original	Floor	Ceiling
2	36.16	14:56:06	14:45:00	15:00:00
3	36.18	14:57:04	14:45:00	15:00:00
4	36.185	14:59:03	14:45:00	15:00:00
5	36.18	15:00:08	15:00:00	15:00:00
6	36.215	15:01:15	15:00:00	15:15:00
7	36.215	15:01:15	15:00:00	15:15:00
Sheet5

 

[pgc01]

Hi brettr87

Try:

In C2: =IF(A2>0,FLOOR(B2,"0:15"))

In D2: =IF(A2>0,CEILING(B2,"0:15"))

Copy down

 

[xld]

Try

=IF(A2>0,ROUNDDOWN(B2*96,0)/96,"")

and

=IF(A2>0,ROUNDUP(B2*96,0)/96,"")

 

[sanrv1f]

may be this

Excel Workbook
	A	B	C	D
1		Original	Floor	Ceiling
2	36.16	14:56:06	14:45:00	15:00:00
3	36.18	14:57:04	14:45:00	15:00:00
4	36.185	14:59:03	14:45:00	15:00:00
5	36.18	15:00:08	15:00:00	15:15:00
6	36.215	15:01:15	15:00:00	15:15:00
7	36.215	15:01:15	15:00:00	15:15:00
Sheet3

Cell Formulas
Range	Formula
C2	=IF(A2>0,TIME(HOUR(B2),FLOOR(MINUTE(B2),15),0),"")
D2	=IF(A2>0,TIME(HOUR(B2),CEILING(MINUTE(B2)+SECOND(B2)/60,15),0),"")

 

[Scott Huish]

Similar idea to pgc01:

C2:
=IF($A2>0,FLOOR($B2,1/96),"")

D2:
=IF($A2>0,CEILING($B2,1/96),"")

Copy down.

 

[brettr87]

Thank you everyone, working wonderfully.