Skip to main content

Formula for multiple products with different Expiry dates

  • April 30, 2020
  • 5 replies
  • 47 views
J

Hey everyone,

I am having trouble creating a formula that would help me with product listings with different expiry dates. My products come in packages of sessions with differing expiry dates.

How do I get a formula that would help me get the expiration date for different products from the date of sale and respective validity period?

For e.g.
Product Listing: 6 sessions training package
Date of Sale: 30/04/2020
Validity period: 30 days

Product Listing: 12 sessions training package
Date of Sale: 12/04/2020
Validity period: 90 days

Product Listing: 6 group training package
Date of Sale: 30/04/2020
Validity period: 30 days

Expiry Column : ?

Regards,
Jeremy

5 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @Jeremy_Tan,

Welcome to Airtable Community!

What you need is
DateAdd({Date of Sale}, {Validity period}, 'days')

Make sure that the validity period is formatted as a number field and the Date of Sale is formatted as a date field.

BR,
Mo

J
  • Jeremy_Tan
  • Author
  • New Participant
  • May 1, 2020

Hi @Jeremy_Tan,

Welcome to Airtable Community!

What you need is
DateAdd({Date of Sale}, {Validity period}, 'days')

Make sure that the validity period is formatted as a number field and the Date of Sale is formatted as a date field.

BR,
Mo


OMGOSH THANKS! It worked! i had used this formula recently but it couldnt work because my validity period was a Lookup and not a number field. Didn’t know that was what was blocking the formula from working!
THANKS!

    Mohamed_Swella1
    Forum|alt.badge.img+17

    OMGOSH THANKS! It worked! i had used this formula recently but it couldnt work because my validity period was a Lookup and not a number field. Didn’t know that was what was blocking the formula from working!
    THANKS!


    Instead of having it as a lookup, make it a rollup. This would work as well.

    Justin_Barrett
    Forum|alt.badge.img+21

    OMGOSH THANKS! It worked! i had used this formula recently but it couldnt work because my validity period was a Lookup and not a number field. Didn’t know that was what was blocking the formula from working!
    THANKS!


    It’s also possible to leave the Lookup in place, and modify the formula to extract the number from the lookup value. If each option begins with a two-digit number, this formula would work:

    DATEADD({Date of Sale}, VALUE(LEFT({Validity period} & "", 2)), 'days')

    As a reminder, Lookup fields return arrays, which is why I concatenated the Lookup field value with an empty string to convert it into a string before doing the rest.

      J
      • Jeremy_Tan
      • Author
      • New Participant
      • May 5, 2020

      It’s also possible to leave the Lookup in place, and modify the formula to extract the number from the lookup value. If each option begins with a two-digit number, this formula would work:

      DATEADD({Date of Sale}, VALUE(LEFT({Validity period} & "", 2)), 'days')

      As a reminder, Lookup fields return arrays, which is why I concatenated the Lookup field value with an empty string to convert it into a string before doing the rest.


      woah this works well tooooo! So glad! thank you!

      Terms & ConditionsAccessibility statement