Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • 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
 
pdvsaAuthor 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
pdvsaAuthor Commented:
that was it.  thank you.  :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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