Forum Discussion
IF(DATEDIFF( ....
Dehlilah So, a few possible improvements:
Expiration Category v2 = SWITCH(TRUE(), ( TODAY() - [Expiration Date] ) * 1. < 0, "Expired", ( TODAY() - [Expiration Date] ) * 1. <= 30, "Expires in 30 days", ( TODAY() - [Expiration Date] ) * 1. <= 60, "Expires in 60 days", "Expires in 180+ days" )So, SWITCH(TRUE(), ...) is much cleaner than nested IF statements. Two, using simple math keeps the computation out of the formula engine which is faster. Dates are the number of days since December 30th, 1899 so you can just use subtraction to get the number of days.
Depending on your data, you may want to reverse your logic. If the majority of the items are 180+ from expiring then you will want that condition first in your SWITCH statement for example. That way the majority of rows do not have to fail a bunch of other logical conditions before getting to the right one.
Community ChampionDehlilah So, a few possible improvements:
Expiration Category v2 =
SWITCH(TRUE(),
( TODAY() - [Expiration Date] ) * 1. < 0, "Expired",
( TODAY() - [Expiration Date] ) * 1. <= 30, "Expires in 30 days",
( TODAY() - [Expiration Date] ) * 1. <= 60, "Expires in 60 days",
"Expires in 180+ days"
)So, SWITCH(TRUE(), ...) is much cleaner than nested IF statements. Two, using simple math keeps the computation out of the formula engine which is faster. Dates are the number of days since December 30th, 1899 so you can just use subtraction to get the number of days.
Depending on your data, you may want to reverse your logic. If the majority of the items are 180+ from expiring then you will want that condition first in your SWITCH statement for example. That way the majority of rows do not have to fail a bunch of other logical conditions before getting to the right one.