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.
 

Some videos you may like

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
Joined
Jul 23, 2007
Messages
4,621
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top