Help With IF & Time Formula :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Help With IF & Time Formula

MrData
Board Master

Joined: 04 May 2003
Posts: 115

Flag:

Status: Offline

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

Fri Sep 19, 2003 10:25 pm

Juan Pablo González
MrExcel MVP

Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag:

Status: Offline

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 ?
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Fri Sep 19, 2003 10:31 pm

MrData
Board Master

Joined: 04 May 2003
Posts: 115

Flag:

Status: Offline

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

Fri Sep 19, 2003 10:38 pm

Juan Pablo González
MrExcel MVP

Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag:

Status: Offline

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. ?
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Fri Sep 19, 2003 10:40 pm

MrData
Board Master

Joined: 04 May 2003
Posts: 115

Flag:

Status: Offline

Re: Help With IF & Time Formula

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

Fri Sep 19, 2003 10:42 pm

Juan Pablo González
MrExcel MVP

Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag:

Status: Offline

Re: Help With IF & Time Formula

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

Juan Pablo González
MrExcel.com Consulting

Fri Sep 19, 2003 10:44 pm

MrData
Board Master

Joined: 04 May 2003
Posts: 115

Flag:

Status: Offline

Re: Help With IF & Time Formula

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

Fri Sep 19, 2003 10:49 pm

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

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!
_________________
Regards!
Yogi Anand

Fri Sep 19, 2003 11:37 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum