Solved: Expiry Dates - Page 2

BlakeHammond · 12-30-2019 03:24 PM

Hi

I am trying to create an if statement expression for expiry dates but getting stuck.

What I am trying to achieve is

If “contract date” is less then todays date then “Live” but if “Contract date” is within 30days of today then “expiring”, if “Contract Date” is greater then today then “Expired”.

Any help would be appreciated.

Blake

Bahbus

IFS(
  [EndDate]<TODAY(), "Expired",
  ([EndDate]-30)<TODAY(), "Expiring",
  [EndDate]>TODAY(), "Live",
  TRUE, "I'm still missing something..."
)

Alright. So I’m 99% sure this what we are looking for. Assuming nothing weird is going on with the formatting. Date-Date gives duration and it can’t be negative. Date-Number on the other hand keeps it a regular date.

So if TODAY()=December 30th, this expression say that the 29th is expired, 31st is expiring, Jan 1st is expiring, and June 1st, 2020 is live.

Let me know my interpretation of your goals is accurate with those same dates. If you meant Expiring to be for up to 30 days after the date is past, you just have to swap the 1st and 3rd lines and then change the -30)< to +30)>.

And if you keep getting errors, I would look into changing things to MM/DD/YY format to see if that’s causing the issue. But let me know either way. I like solving puzzles.

View solution in original post