Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formula for multiple products with different Expiry dates

Topic Labels: Formulas
551 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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 5

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!

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

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!