Forum Discussion

MichaelJackpbi's avatar
MichaelJackpbi
Icon for Helper I rankHelper I
9 years ago
Solved

Add a custom column to track the date

Hi Friends, I have a exel file including itemcode,expireddate. Now i need to add a new column naming Expired_Days to calculate the number of days from today to the expired date for every item. But w...
Need Help
  • AlexChen's avatar
    AlexChen
    9 years ago

    Hi,

     

    I assume your table called “itemInfo”. You can use following formula to calculate expired days

     

    Expired_Days = value(TODAY()) - value(itemInfo[expireddate])

     

    You can use formula below to calculate its expired status.

     

    Expired_status = if(itemInfo[Expired_DAys] < 0, "Not Expired",

                                  if(itemInfo[Expired_DAys] < 30, "Less than 30",

                                  if(itemInfo[Expired_DAys] >= 30 && itemInfo[Expired_DAys] < 60, " more than 30 and less than 60",

                                  "more than 60")))

     

     

    Best Regards

    Alex

     

AlexChen's avatar
AlexChen
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi,

 

I assume your table called “itemInfo”. You can use following formula to calculate expired days

 

Expired_Days = value(TODAY()) - value(itemInfo[expireddate])

 

You can use formula below to calculate its expired status.

 

Expired_status = if(itemInfo[Expired_DAys] < 0, "Not Expired",

                              if(itemInfo[Expired_DAys] < 30, "Less than 30",

                              if(itemInfo[Expired_DAys] >= 30 && itemInfo[Expired_DAys] < 60, " more than 30 and less than 60",

                              "more than 60")))

 

 

Best Regards

Alex