Solved

Conditional Formatting - Date()

Posted on 2016-11-20
8
40 Views
Last Modified: 2016-11-21
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
Comment
Question by:pdvsa
8 Comments
 
LVL 19
ID: 41895444
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
 

Author Comment

by:pdvsa
ID: 41895463
thank you.
How should I enter that in the msg box?  
I get a syntax error.  

syntax
0
 
LVL 19
ID: 41895472
instead of Field Value is, choose: Expression Is

[due date]  >=( Date()-1 ) and [due date] < (Date() +1)
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41895477
Hi,

Pls try

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

Regards
0
 
LVL 19
ID: 41895481
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41895527
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41895723
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
 

Author Closing Comment

by:pdvsa
ID: 41895725
that was it.  thank you.  :)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question