Conditional Formatting - Date()

Experts, I have been trying to shade a cell based on if it is between Date() and Date()-1.  It doesn't seem to work as I have it (see pic).

Value is between Date() and Date()-1

thank you
duedate
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Gustav BrockConnect With a Mentor CIOCommented:
I recall that for some reason your dates are text. If not, if they were true date values, your original statement would work.

So try converting to date values:

    Expression is DateValue([due date]) Between Date()-1 And Date()

/gustav
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
dates are store as Date AND Time. While beginning date may work to filter for the start, Date() may not for the end since there may also be a time. Instead, use:
>=( Date()-1 ) and < (Date() +1)

Dates are stored as whole numbers.
Time is a fraction through the day. for instance, 6am is 0.25, noon is 0.5, and 6pm is 0.75

When assigning values, use Date(), not Now() if all you want is date
0
 
pdvsaProject financeAuthor Commented:
thank you.
How should I enter that in the msg box?  
I get a syntax error.  

syntax
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
instead of Field Value is, choose: Expression Is

[due date]  >=( Date()-1 ) and [due date] < (Date() +1)
0
 
Rgonzo1971Commented:
Hi,

Pls try

Field Value is / between  / Date()-1 / Date()+1

Regards
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Rgonzo1971, that will only work whern there always is an extra time component ... there may not be.  The solution needs to work with all the data

DateValue([due date] ) can also be compared but that creates another issue in that you must first test to make sure it is filled since you can't use a conversion function on Null.  Easiest is to get everything less than the next day for the end date.
0
 
Dale FyeCommented:
you might also want to try:

Field Value:  >= Date() - 1 AND < Date() + 2

or, if the date is actually stored as a string:

Expression: (CDATE([DateField]) >= Date() -1) AND (CDATE([DateField] < Date() + 2)
0
 
pdvsaProject financeAuthor Commented:
that was it.  thank you.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.