Help With IF & Time Formula :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Help With IF & Time Formula
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

MrData
Board Master


Joined: 04 May 2003
Posts: 115

Flag: Uk

Status: Offline

 Reply with quote  

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. icon_banghead.gif 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

Post Fri Sep 19, 2003 10:25 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 10:31 pm 
 View user's profile Send private message Visit poster's website

MrData
Board Master


Joined: 04 May 2003
Posts: 115

Flag: Uk

Status: Offline

 Reply with quote  

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

Post Fri Sep 19, 2003 10:38 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 10:40 pm 
 View user's profile Send private message Visit poster's website

MrData
Board Master


Joined: 04 May 2003
Posts: 115

Flag: Uk

Status: Offline

 Reply with quote  

Re: Help With IF & Time Formula

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

Post Fri Sep 19, 2003 10:42 pm 
 View user's profile Send private message

Juan Pablo González
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Read the Articles List and check out our Recommended links and Add-Ins

Post Fri Sep 19, 2003 10:44 pm 
 View user's profile Send private message Visit poster's website

MrData
Board Master


Joined: 04 May 2003
Posts: 115

Flag: Uk

Status: Offline

 Reply with quote  

Re: Help With IF & Time Formula

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

Post Fri Sep 19, 2003 10:49 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Post Fri Sep 19, 2003 11:37 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


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

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.