IF Logical Test for Time Range In Cell

[Bob McCusker]

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.

 

[Trebor76]

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]

i agree with trebor, the only way out here to use Lower function Like -

=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]

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!

 

[Jon von der Heyden]

Cross-posted here: http://www.excelforum.com/excel-general/738477-if-logical-test-for-time-range-in-cell.html

Please, if you choose to cross-post in future, let us know that you have done so and provide links to the other threads. That way we can see if a solution has already been provided.

 

[Bob McCusker]

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]

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

=IF(AND(B1>=time(7,0,0),B1<=time(23,59,0)),"A1","")

With thanks,
Sukumar