+ Reply to Thread
Results 1 to 9 of 9

IF Logical Test for Time Range In Cell

  1. #1
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile IF Logical Test for Time Range In Cell

    Hello -

    I have 3 cells:

    A1 is the day
    B1 is the time (military format)
    C1 should show "A1" IF B1 is between 7:00 & 23:59, or show "A1-1" [a day before A1] if B1 is between 0:00 & 6:59.

    I know how to use IF somewhat, but can't figure the logical test for a time range. The closest I have is:

    =IF(AND(B1>=7:00,B1<=23:59),"A1","")

    For the first part, which returns an error.


    Also, this is sort of nitpicking, but does anyone know of a way that I can get a day cell to show "wed" instead of "Wed"? I pull the day cell from a date cell so I can't do it manually. This part isn't really that important its just more or less bothering me that I can't figure it out.

    Any help would be greatly appreciated.
    Last edited by Bob McCusker; 07-23-2010 at 07:26 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: IF Logical Test for Time Range In Cell

    I don't know what military format is but looking at your example:

    =IF(B1="7:00", A1, "")

    Note: You need to use "" around text and time (i.e. "test" and "23:59") and NOT around cells and numbers (i.e. "A1" and "10")


    For second part use PROPPER function to write capital first letter:

    =PROPPER(C1)

    Edit: yes, LOWER function for all small letters.
    Last edited by zbor; 07-23-2010 at 02:03 AM.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF Logical Test for Time Range In Cell

    A few options

    =A1-(HOUR(A1)<7)

    Regards Wed - not clear... perhaps you mean

    =LOWER(TEXT(C1,"ddd"))

    @zbor:

    You need to use "" around text and time
    If you use strings you should coerce the string to number - ie 0+"7:00" else "7:00" is greater than "11:00"
    Last edited by DonkeyOte; 07-23-2010 at 02:03 AM.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: IF Logical Test for Time Range In Cell

    Bob,

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this.

    http://www.mrexcel.com/forum/showthread.php?t=483213

  5. #5
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: IF Logical Test for Time Range In Cell

    @zbor

    Military format is using the 24:00 format. My cell displays 14:00 for instance, but when I click on the cell it shows 02:00:00 PM.

    I didn't know about the quotes, thanks. I tried that with my existing formula, but it still doesn't work however.

    @DonkeyOte

    The formula: =A1-(HOUR(A1)<7) returns the day before A1 no matter what time is entered in B1.

    @Paul

    My regrets. This forum is invaluable and I am reading the forum rules thread now so I do not break any in the future!

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF Logical Test for Time Range In Cell

    Sorry the formula should be:

    =A1-(HOUR(B1)<7)

  7. #7
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: IF Logical Test for Time Range In Cell

    For anyone curious, this formula ended up working:

    =IF(AND(HOUR(B1)>=7,HOUR(B1)<=23),A1,A1-1)

    However, when I changed A1 to lowercase using the LOWER function, the formula returns #VALUE! when the above false value (A1-1) is used.

    I also tried to leave A1 and use this formula for lowercase:

    =LOWER(IF(AND(HOUR(B1)>=7,HOUR(B1)<=23),A1,A1-1))

    but I get numbers like 40370 for example and not the day in ddd.


    I'm not sure this can be done (lower casing it) but still functional in any case.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: IF Logical Test for Time Range In Cell

    As outlined you don't need the IF

    =A1-(HOUR(B1)<7)

    The HOUR part will evaluate to either TRUE or FALSE - these Boolean/Logical values when coerced (via subtraction) equate to 1 and 0 respectively hence:

    =A1-TRUE -> A1-1
    =A1-FALSE -> A1-0

    Regards LOWER - you're missing the TEXT part from the earlier suggestion also.

    A Date is a number (as you've seen) - if you want to convert the date interpretation to lower case you must first convert the number to an appropriate date string (ie via TEXT)

    So re-working the earlier example:

    =LOWER(TEXT(A1-(HOUR(B1)<7),"ddd"))

  9. #9
    Registered User
    Join Date
    10-24-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: IF Logical Test for Time Range In Cell

    @DonkeyOte

    That works fantastic! Thanks for the thorough explanation as well; I understand how its working now. I wasn't quite getting it at first.

    Also, in case anyone ever searches this topic; I kept having problems getting both A1 & C1 to be lower case because for C1, I was referencing A1 instead of the 'date cell' directly. This was causing only one of C1/A1 to display correct, the other one to fail.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1