Upcoming dates, #error, and formatting

[Kirsty_Kavanagh]

I run a pet relocation company and we have many clients who hold their pet files with us in case of a future move. We need a formula that red flags when a vaccination expiry date is imminent (e.g. in 14 days, in 28 days, etc). Really struggling with how we can incorporate this into airtable.

[Mohamed_Swella1]

Hi @Kirsty_Kavanagh,

Welcome to Airtable Community! :slightly_smiling_face:

You can create a formula field that is something like

DateTime_Diff({Vaccination Exp Date}, Today(), 'days')

This will give you the number of days till vaccination expires. You can then create views (or add it in the formula if you want) that filters when this number is 28 or less, 14 or less, and so on.

Hope that helps. If it does, please mark this as a Solution so others can see it.

BR,
Mo

[Kirsty_Kavanagh]

Hi

Thank you Mohamed. That sounds exactly what we need. How does the flag work? Do we need an additional field for a flag to show, or does it highlight the record. If the latter, how does it highlight it?

(Attachment image001.tiff is missing)

[kuovonne]

You can use nested IF statements with DATETIME_DIFF in a formula field:

IF(
  DATETIME_DIFF({vaccineExpiryDate), TODAY(), 'days') < 0,
  "vaccine overdue",
  IF(
    DATETIME_DIFF({vaccineExpiryDate), TODAY(), 'days') < 14,
    "vaccine due very soon",
    IF(
      DATETIME_DIFF({vaccineExpiryDate), TODAY(), 'days') < 28,
      "vaccine due soon"
    )
  )
)

If you want the record to be associated with a color (and you have a pro subscription) you can use record coloring.

[Mohamed_Swella1 · Accepted solution]

You’re welcome Kristy :slightly_smiling_face:

There are several ways to do so.

I personally would use the Flag in the formula. So it becomes something like:

IF(DateTime_Diff({Vaccination Exp Date}, Today(), 'days')<=14,"⚠️ 14 Days or Less Till Vaccination Expiry", IF(DateTime_Diff({Vaccination Exp Date}, Today(), 'days')<=28,"28 Days or Less Till Vaccination Expiry")

This works for 14 days and 28 days, if you want to add more warnings, you can add more IF statements.

Feel free to add emojis to your formula from here

Your screenshot was not attached btw.

BR,
Mo