IF formula is too long, can it be shortened?

[gordymoore5]

I have made an IF formula to work out starting hours on a timesheet, were lateness is rounded up.
If somebody clocks in 3 minutes past the quarter hour they are fine, four minutes past and it goes to the next 15 minutes.
for example: 6.03 is 6.00 start, but 6.04 is 6.15 start.
here is the formula i have but i would like to simplify it by getting rid of every hour any saying if the time is *:03 = 6.00 and if its *.04 = 6.15.

=IF($B$6<5.03,"5.00",IF($B$6<5.18,"5.15",IF($B$6<5.33,"5.30",IF($B$6<5.48,"5.45",IF($B$6<6.03,"6.00",IF($B$6<6.18,"6.15",IF($B$6<6.33,"6.30",IF($B$6<6.48,"6.45"))))))))

Any ideas?????

[gordymoore5]

Here is a copy of what i have so far

[zbor]

Here, try this:

Please Login or Register  to view this content.

[gordymoore5]

results in =N/A

[bebo021999]

Is the B6 formated as time?
If yes, try this:

Please Login or Register  to view this content.

If no, we have to convert time in TEXT to TIME format.

Edit: Post sent before finding the new post from OP with file attached. Will be back soon.

[Norie]

Where are you actually using this formula?

I can't seem to find it anywhere in the workbook.

I did find this similiar formula in C6:C12 on 'my play'.

Please Login or Register  to view this content.

[gordymoore5]

that is the closest i have had yet, i have changed to time format but would prefer it in text format so 06:30:00 is 6.30
i also need the formula for finish times too, if possible

[gordymoore5]

the formula may be slightly different, it is formula in cells C6 and E6 i am after

[bebo021999]

Try in C6:

Please Login or Register  to view this content.

Could you pls explain the rule for finish time?

[gordymoore5]

rule is paid to nearest quarter hour.

[gordymoore5]

Is the B6 formated as time?
If yes, try this:

Please Login or Register  to view this content.

If no, we have to convert time in TEXT to TIME format.

Edit: Post sent before finding the new post from OP with file attached. Will be back soon.

This formula has worked best so far, except if the time is 7:48 - it doesnt change to 8:00

[bebo021999]

This formula has worked best so far, except if the time is 7:48 - it doesnt change to 8:00

You said that if 3 minutes past the quarter is OK, that means 7:48 should be 7.45, is that right?
By the way, does the formula with TEXT in #9 work?

[gordymoore5]

Yes 7.48 should be 7.45. no the text formula didnt work