Help

Expiry Date formula

Topic Labels: Formulas
1528 1
cancel
Showing results for 
Search instead for 
Did you mean: 
PJ_Saysell-Rosa
4 - Data Explorer
4 - Data Explorer

Hello!

I have created a food management base for my film production company and I want to create a formula that will flag food products that are:

-Expired
-Expiring within 28 days

The name of the field with the dates in is “Use by Date”.

Thank you!

1 Reply 1

Here’s what I came up with:

Screen Shot 2020-01-24 at 7.01.52 AM

I went with two formula fields: one to calculate the date difference between {Use by Date} and the current day, and another to use that difference to decide how to flag each item. This could all be done in a single formula, but whenever I’m using the same calculation several times, I prefer to throw that into its own formula field and treat it like a programming variable. {Diff} is this:

DATETIME_DIFF({Use by Date}, NOW(), "days")

And {State} is this:

IF(Diff <= 0, "❌", IF(AND(Diff > 0, Diff <= 28), "⚠️"))