IF Logical Test for Time Range In Cell

Bob McCusker

New Member
Joined
Oct 24, 2009
Messages
42
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
Joined
Jul 23, 2007
Messages
4,945
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
Joined
May 29, 2009
Messages
355
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
Joined
Oct 24, 2009
Messages
42
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
Joined
Oct 24, 2009
Messages
42
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
Joined
Jan 22, 2017
Messages
1
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top