# IF Logical Test for Time Range In Cell

1. ## 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.  Register To Reply

2. ## 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.  Register To Reply

3. ## 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"  Register To Reply

4. ## 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!  Register To Reply

5. ## 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!  Register To Reply

6. ## Re: IF Logical Test for Time Range In Cell

Sorry the formula should be:

=A1-(HOUR(B1)<7)  Register To Reply

7. ## 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.  Register To Reply

8. ## 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"))  Register To Reply

9. ## 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.  Register To Reply