IF Statement with Greater Than, Less Than Dates

[andrew_sell]

Hey guys,

Need some help on an IF statement where I am trying to categorize a line item based on an date column in the data. I want to create three different buckets based on the current day.

If the date column (End Date) I am using is less than today's date, I want that line to be marked 'Expired'. If the (End Date) is greater than today's date and less than 90 days after today's date, i want to mark it 'Expiring'. If the (End Date) is greater than 90 days from today's date, i want to mark it 'Active'.  Here is the expression i currently have been playing with.

IF([End Date] < 'Date(Today(), M/D/YYYY)', 'Expired', IF([End Date] >= 'Date(Today(), M/D/YYYY)' and [End Date] <= 'Date(Today()+90, M/D/YYYY))', 'Expiring', IF([End Date] > 'Date(Today()+90, M/D/YYYY))', 'Active', 'Null')))

I am sure i am missing something simple, but need some fresh eyes to help me i think.

Thanks!

[petter]

Don't put single quotes around this:

   Date(Today(), M/D/YYYY)

Do put quotes around the format string:

Date(Today(), 'M/D/YYYY')

If the [End Date] field is a true date field then this should be sufficient:

  If( [End Date] = Today() , .......

[sunny_talwar · Accepted solution]

May be simplify your statement to just this (adding to what petter-s‌ said)....

If([End Date] < Today(), 'Expired',

If([End Date] <= Today()+90, 'Expiring',

If([End Date] > Today()+90, 'Active', 'Null')))