Skip to main content

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

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

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!

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.

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.

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!

Reply


Terms & Conditions