Formula for multiple products with different Expiry dates

[Jeremy_Tan]

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

[Mohamed_Swella1]

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

[Jeremy_Tan]

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]

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

[Justin_Barrett]

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.

[Jeremy_Tan]

Justin_Barrett:

DATEADD({Date of Sale}, VALUE(LEFT({Validity period} & “”, 2)), ‘days’)

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