I'm currently using the formula below to enter the time based on the contents of a cell:-
=IF(ROUND(B14-INT(B14),2)=0,INT(B14),IF(AND(ROUND(B14-INT(B14)<=0.15,B14-INT(B14)>0),2),INT(B14)+0.15,IF(AND(ROUND(B14-INT(B14),2)<=0.3,B14-INT(B14)>0.15),INT(B14)+0.3,IF(AND(ROUND(B14-INT(B14)<=0.45,B14-INT(B14)>0.3),2),INT(B14)+0.45,INT(B14)+1))))
By using the formula I'm able to convert 20.20 into 20.30, or 15.10 into 15.15
What I would like to do is enter any time between 06.01 and 17.59 as "Pre 18.00" (05.05 would show Pre 18.00) and any other time rounded to the nearest quarter of an hour. Can anyone assist as I've spent the last two hours trying to do this and am getting no-where.
Also I'm using the 24hour clock.
Cheers
Mike
=IF(ROUND(B14-INT(B14),2)=0,INT(B14),IF(AND(ROUND(B14-INT(B14)<=0.15,B14-INT(B14)>0),2),INT(B14)+0.15,IF(AND(ROUND(B14-INT(B14),2)<=0.3,B14-INT(B14)>0.15),INT(B14)+0.3,IF(AND(ROUND(B14-INT(B14)<=0.45,B14-INT(B14)>0.3),2),INT(B14)+0.45,INT(B14)+1))))
By using the formula I'm able to convert 20.20 into 20.30, or 15.10 into 15.15
What I would like to do is enter any time between 06.01 and 17.59 as "Pre 18.00" (05.05 would show Pre 18.00) and any other time rounded to the nearest quarter of an hour. Can anyone assist as I've spent the last two hours trying to do this and am getting no-where.
Also I'm using the 24hour clock.
Cheers
Mike