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! Go to 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.
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.
That corrected that issue but created another
โIFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairsโ.
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 :).
User | Count |
---|---|
42 | |
28 | |
24 | |
22 | |
13 |