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:

=LOOKUP(B6,{0,5.03,5.18,5.33,5.48,6.03,6.18,6.33,6.48},{5,5.15,5.3,5.45,6,6.15,6.3,6.45})

[gordymoore5]

results in =N/A

[bebo021999]

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

=TIME(HOUR(B6),IF(MINUTE(B6)<4,0,IF(MINUTE(B6)>48,60,IF(MINUTE(B6)<19,15,IF(MINUTE(B6)<34,30,45)))),0)

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.

[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]

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

=TIME(HOUR(B6),IF(MINUTE(B6)<4,0,IF(MINUTE(B6)>48,60,IF(MINUTE(B6)<19,15,IF(MINUTE(B6)<34,30,45)))),0)

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

[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'.

=IF(B6<7.33,"7.30",IF(B6<7.48,"7.45",IF(B6<8.03,"8.00")))

[gordymoore5]

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

[bebo021999]

Try in C6:

=(LEFT(B6,2)+IF(--RIGHT(TEXT(B6,".00"),2)>48,1,0))&"."&IF(OR(--RIGHT(TEXT(B6,".00"),2)<4,--RIGHT(TEXT(B6,".00"),2)>48),"00",IF(--RIGHT(TEXT(B6,".00"),2)<19,15,IF(--RIGHT(TEXT(B6,".00"),2)<34,30,45)))

Could you pls explain the rule for finish time?

[gordymoore5]

rule is paid to nearest quarter hour.