Forum Discussion

Dehlilah's avatar
Dehlilah
Regular Visitor
2 years ago
Solved

IF(DATEDIFF( ....

I am really close to my desired outcome, but need to figure out a small change. I have a product that expires after two years. I have data on the order date, so I made a column called "Expiration Date" which is simply two years after the order date. 

 

I created this coding: 

 

Expiration Category v2 =
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<0,"Expired",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=30,"Expires in 30 days",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=60,"Expires in 60 days",
IF(DATEDIFF(TODAY(),'Table'[Expiration Date], DAY)<=180,"Expires in 180+ days", "Expires in 180+ days"
))))
 
I want to know the amount of quantity that has expired, will expire soon (30 days or in 60 days) and 180+, but really I could change the last outcome 180+ to anything else or >61 days from expiration. Can someone help with this change? As I have it now the code doesn't capture total shippments, so something is off.
 
Thank you! 
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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. 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.