Expiry Dates

BlakeHammond
Participant IV

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

Solved Solved
0 11 1,731
1 ACCEPTED SOLUTION

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

11 REPLIES 11

Bahbus
Participant V

IFS([Contract Date]>TODAY(), "Expired", TODAY()-[Contract Date]<30, "Expiring", [Contract Date]<TODAY(), "Live", "Something Went Wrong")

Just substitute the name of your column for the column I used here.

@Bahbus Thanks for your help with this. I have changed the column names but i get an error “Arithmetic expression ‘((TODAY()-[Contract End Date]) < 30)’ does not have valid input types”

IFS([Contract Date]>TODAY(), "Expired", HOUR(TODAY()-[Contract Date])<720, "Expiring", [Contract Date]<TODAY(), "Live", "Something Went Wrong")

Oh yeah. I forgot that it creates the Duration type and it doesn’t like getting compared to numbers directly.

BlakeHammond
Participant IV

That corrected that issue but created another

“IFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairs”.

Bahbus
Participant V

IFS([Contract Date]>TODAY(), “Expired”, HOUR(TODAY()-[Contract Date])<720, “Expiring”, [Contract Date]<TODAY(), “Live”, TRUE, “Something Went Wrong”)

@Bahbus Couple small issues

If i Set the contract end date to the following i get the following issue

29/12/19 - Expired instead of expiring as it is within 30 days of today

31/12/19 - Something went wrong instead of Live

1/1/2020 - “Function HOUR parameter 1 value is negative. It should be positive or zero.” if I change the - for a + I get “live”.

I changed the < & > around as i needed them the opposite way round

Thanks

Blake

Ah, I was just building it off your initial description. I wasn’t in an editor at all to double check if it was working in the right direction. And I don’t think there should be an issue with DD/MM/YY over MM/DD/YY.

29th was yesterday, so it is expired. Unless you meant that you want a 30 day buffer after the contract end date.

31/12/19 triggering Something Went Wrong means it didn’t match any of the other 3 arguments, when it should have said Expired based off my initial (wrong direction) expression. This is the only evidence I have for something not right with the date format.

I think I know how to fix this. It’s mostly the logic flow of the IFS. I just need to get home to my computer so I can jump on the editor and test a thing or two.

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.

@Bahbus That works a treat thank you very much. I pretty much got to the same answer

“if([Contract End Date]>today(),“Live”,
IF(today()-30<[Contract End Date],“expiring”,
if([Contract End Date]<today(),“Expired”,“t”)))”

but I didn’t think about the IFS statement so your cleaned it up for me

I appreciated your help with this

Blake

No worries. The important part is the order. You want to check for Expiring before Live, since there are are dates that both would be true, we want to check for Expiring first, so that it will skip checking Live. And then with the advantage of the IFS, it stops as soon as finds something true (which is also why I put that catch all at the end for easy debugging), so it should be a quick and light expression.

Ah, Makes sense didn’t think about the order as it worked when I tried it. I have used the IFS now so is in the same order as yours. So hopefully shouldn’t have that issue :).

Top Labels in this Space