Help With IF & Time Formula

MrData

Active Member
Joined
May 4, 2003
Messages
324
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. :oops: 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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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 ?
 
Upvote 0
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
 
Upvote 0
Hello MrData:

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top