• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
1 Solution
 
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now