Issues with date based formatting

[#1]

Hi,

I'm trying to set up a basic straight table chart, that lists service desk tickets and highlights ones that are past the agreed SLA time based on a priority. So for example a priority 2 should be highlighted if it's older than 2 weeks.

For basic testing I set an expression up with the following on the background colour property using the addmonths function -

if ([Date raised] < addmonths(NOW(),-1),rgb(255,0,0), rgb(255,255,255))
However there's only addmonths and addyears, and I need to work on day and week values. I tried using the age function by adding a new column with the age in days (I figured I could then use this to drive the formatting) but oddly this only seems to work on certain rows of data, others just don't get a value. The expression is

(Date(Now()) -  Date([Date raised]),'D')

So for example my table looks like this -

Ticket number     Date raised          Age
1                         05/02/2014          498
2                         17/06/2014          -

3                         22/09/2014          -

4                         10/01/2014          524

I can't see any difference between the rows that work and those that don't. Does anyone know either an easier way to do this, and/or why the age doesn't seem to work properly?

[#2]

hi,

Try this if (Now()-Date#([Date raised])>7,rgb(255,0,0), rgb(255,255,255))

[Gysbert_Wassenaar · Accepted solution]

Check the default date format of your document. If that format is MM/DD/YYYY then 05/02/2014 is be interpreted as May 2nd 2015. But there are only 12 months so 17/06/2014 can't be interpreted as a valid date. Your Date field might contain both valid dates and text values. So your Age column would get a null value if there's no valid date.

[#4]

Thanks, I edited my data load in my script to force the date format and then all the values came back in the table -

date(num([Date raised]),'DD/MM/YYYY') as [Date raised]