Help With IF & Time Formula

Thanks:  0
Likes:  0

# Thread: Help With IF & Time Formula

1. ## Help With IF & Time Formula

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

2. ## Re: Help With IF & Time Formula

Wow that's one huge formula ! I think that's doable in other (simpler) matters, but I don't completely follow what you're after. Can you post some examples and the corresponding results ?

3. ## Re: Help With IF & Time Formula

Example of what I'm after is shown below. Column B shows the original time, Column A the revised using the formula. Where the original time is between 06.01 and 17.59 it currently means having to manually enter "Pre 18.00

A B
24.00 00.00
0.15 00.05
0.30 00.20
0.30 00.30
Pre 18.00 14.12
1.00 01.00
Pre 18.00 15.10
2.15 02.05
2.30 02.20
2.45 02.35
Pre 18.00 16.30

Any ideas?

Thanks

4. ## Re: Help With IF & Time Formula

One more question, the 24.00 and the others are "real" time values ? or a number like that ? 24.00, 0.15, etc. ?

5. ## Re: Help With IF & Time Formula

Not sure I understand you fully, but I think they are real time values.

6. ## Re: Help With IF & Time Formula

If you select the cell that has 16.30, what shows in the formula bar ?

7. ## Re: Help With IF & Time Formula

16.30 is shown as 16.30. What I would like it to say is Pre 18.00

8. ## Re: Help With IF & Time Formula

Hello MrData:

Have you considered using a VLOOKUP table, or even hardcoding one within a formual -- Just A Thought!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•