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.

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.

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"

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!

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!

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

Sorry the formula should be:

=A1-(HOUR(B1)<7)

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.

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"))

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.

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

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