# 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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### 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

Replies
4
Views
172
Replies
2
Views
370
Replies
12
Views
519
Replies
3
Views
206
Replies
8
Views
832

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,492
Messages
5,832,011
Members
430,103
Latest member
BIGGAZ

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back