Date Add conditional Format <30 Days from Today

[NDD]

I have fields with expiration dates. I am attempting to conditional format them to show a color when the date is 30 days or less from today.

I have:
Value<=DateAdd("d",30,Now())

It's working bit it's also formatting all of the dates that are 1/31/2014 for some reason. Not any other 2014 dates, just 1/31/2014.

Any suggestions?

 

[CJ_London]

try

DateAdd("d",30,Date())

to see if it makes a difference

Also

Value - is this just your posting or you are you actually using it in your conditional formula, in the conditional formatter you should be selecting

Field Value Is
less than
DateAdd("d",30,Date())

 

[NDD]

Thanks CJ,
Yes, I have
Field Value Is less than DateAdd("d",30,Date())

Changing it from Now() to Date() did not make a difference

 

[NDD]

I am doing multiple fields at the same time by the way.

 

[CJ_London]

Stuck for suggestions - you could try compacting your db to see if that solves the problem

 

[NDD]

Tried the compact and repair.

The dates are mostly the last day of a month. It's formatting 10/31/13 correctly, but it's also lighting up all the 1/31/2014, but nothing else in 2014.

 

[NDD]

It has something to do with the month of January. No matter what date I put in 1/?/2014, it formats it. It doesn't do February. Strange.

 

[CJ_London]

Sorry, only other things I can suggest is

• rebuild the form or report
• check your dateformats/international settings are correct

 

[NDD]

I think I see what the problem is now. The 1/31/2014 dates that are formatting are text fields in my table. The ones that are not are Date/Time (short date). I had to set some fields to text so I could put "Exempt" in some of them. The ones that are not exempt have dates.

What I don't understand is why it's only picking up January.

 

[namliam]

because in text 1/ is less than 11/
while 2/ obviously is greater than 11/

When working with dates/numbers in text strings you have to make sure they are all the same thing/length for it to work properly....
Dates, use ISO format YYYYMMDD or YYYY/MM/DD ... or use cdate or something to (re)convert them before filtering.
Numbers, prefix them by zero's 001,010,999 for numbers up to one thousand adding zero's as you go along.

 

[NDD]

because in text 1/ is less than 11/
while 2/ obviously is greater than 11/

When working with dates/numbers in text strings you have to make sure they are all the same thing/length for it to work properly....
Dates, use ISO format YYYYMMDD or YYYY/MM/DD ... or use cdate or something to (re)convert them before filtering.
Numbers, prefix them by zero's 001,010,999 for numbers up to one thousand adding zero's as you go along.

Thank you namliam. The format I use for date fields is the shortdate. 1/31/2014

If I'm going to use this same format in a text field, do I need to change all my date formats to YYYY/MM/DD or is there something I can add to the text field date to make it work?

I actually thought about that and added a 0 to the date in the text field date, but it didn't work.

 

[CJ_London]

where you have text dates you could try

Expression Is
Datevalue(DateFld)<DateAdd("d",30,Date())

which will work even if DateFld is the field you are want to contidionally format

But you may need to add some more code if Datevalue is null or not a date

 

[namliam]

as long as dates are stored as a date field, you can have it in any format you like... and work with it in any date-way you want to and have it work just fine without any problems.

Problems come with dates (or numbers) stored as text, then and only then do you need to take additional stuff into consideration.... like I said earlier or the example that CJ gave that you can (re) convert it to a date before manipulating/sorting it.... only thing to worry about is about blanks/nulls like your Exempt values in this case... as well as any other non-date values, which may include wrongly keyed dates even if just something simple like 1/125/2013 instead of 11/25/2013 since on the text field there is no validation of dates...

Working with this is an issue waiting to happen, IMHO, you would be better of with i.e. one proper date field and a tick box "Exempt" which you can easily check against AND be sure that dates are dates and are proper dates.

 

[NDD]

Ok guys, thanks. I'll make the adjustments

 

[NDD]

I got the 30 day to work.
Now I'm trying to do another one for a year.
I've tried Value<Dateadd(d,365,Date())
and Value<Dateadd(YYYY,1,Date())
And neither is working.
What I am trying to do is conditional format a date that is over 1 year old.

Any suggestions?

 

[CJ_London]

you are missing the quotation marks around the d

 

[NDD]

Thanks CJ.
It puts them in automatically. This is cut and paste from the conditional formatting window:

Value >DateAdd("d",365,Date())

 

[CJ_London]

So what is not working?

Just to check, if you have multiple conditions, you need to ensure they are in the right order since the formatting ceases once the condition is met e.g.

condition 1 value>5 - red
condition 2 Value>10 blue

will return red for a value of 11, since 11 is greater than 5

you need to put the other way round

condition 1 Value>10 blue
condition 2 value>5 - red

 

[NDD]

Thanks,
This is the only condition.

It's not formatting the column. I have some dates from 2012 (obviously more than 365 days) and it doesn't change the text or the background. I have the background set to normal, so I didn't make that mistake again.

 

[CJ_London]

What I am trying to do is conditional format a date that is over 1 year old.

This works for me

s