Help With IF & Time Formula

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Help With IF & Time Formula

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    May 2003
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    May 2003
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help With IF & Time Formula

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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    May 2003
    Posts
    323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com