# IF Logical Test for Time Range In Cell

#### Bob McCusker

##### New Member
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Trebor76

##### Well-known Member
Hi Bob McCusker,

Try this formula (just format the result as desired):

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

Though you can use = LOWER(A1) to return a lower case result for the text value in cell A1, there's no format way (that I know of) to convert a day to lower case. Someone else here no doubt will.

HTH

Robert

#### G2K

##### Active Member
i agree with trebor, the only way out here to use Lower function Like -
Code:
``=LOWER(IF(AND(HOUR(B2)>=7,HOUR(B2)<=23),A2,A2-1))``
again, Last part of function "A2-1" won't work as we can not substract day Like Mon-1 = Sun.

#### Bob McCusker

##### New Member
The first formula works perfect (minus the lowercase)! When I change the times it changes accordingly.

I went back and changed the first day cell (A1), which pulled from a date, to lower fine using =LOWER(TEXT([date cell],"ddd")). When I tried the C1 formula again, it doesn't work when it tries to subtract A1-1 like you said.

There might just be no way to do it, but it's cool, this is still very functional. Thanks a bunch!

#### Bob McCusker

##### New Member
Yes... Again, my apologies! I was pointed that out in the other thread and made sure to share the solution with them. And I will not double post in the future (unless the first one goes unanswered, upon which I will post a link!).

#### sukumar

##### New Member
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.
Hi,

Did you try: -

Code:
``=IF(AND(B1>=time(7,0,0),B1<=time(23,59,0)),"A1","")``
With thanks,
Sukumar

1,106,636
Messages
5,512,533
Members
408,903
Latest member