Skip to main content

IF statement to return specific date

  • September 20, 2022
  • 2 replies
  • 24 views
L

Hi brains trust!

Need some guidance here as what I think should be simple is proving not-so.

I’m trying to track gifts given over time and those that are recurring annually.

I have created a column for OCCASION (single select from a list of pre-determined occasions to track), a column for OCCASION DATE (manual input) and would like to add a third column (RECURRING OCCASION) for those occasions that have static and annually reoccurring dates - i.e. Christmas. Would like to include Easter, Father’s Day, Mother’s Day in this column too - returning a date for the 1st day of the month in which they fall (not too worried about specifics here as dates change annually and figuring out a formula for that would fry my poor neurons).

The idea being that any event recurring annually I would simply have to select from the OCCASION column and the RECURRING OCCASION column would auto-populate with the specific date.

I would then create a view to show each year’s budget by month, inclusive of all events, recurring or not.

Please help!

    2 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31
    • TheTimeSavingCo
    • Brainy
    • 6460 replies
    • September 20, 2022

    Hi Lauren, I think this should do what you’re looking for:

    Only caveat is that you’d have to modify the formula to handle the different years, but you could potentially have a Year column for that too I guess

    DATETIME_PARSE(
  SWITCH(
    {Occassion},
    "Easter", "1 Apr",
    "Father's Day", "1 June",
    ''
  ) & "2022",
  "DD MMM YYYY"
)
    Adam, Airtable Consultant - thetimesaving.company
    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9808 replies
    • September 20, 2022

    Hi @LaurenAdrian_PreteGr,

    There seems to be a few typos with @Adam_TheTimeSavingCo’s formula, but this should work for you:

    IF({Occasion},
DATETIME_PARSE(
  SWITCH(
    {Occasion},
    "Easter", "1 Apr",
    "Father's Day", "1 Jun"
  ) & " 2022",
  "D MMM YYYY"
))


    Terms & ConditionsAccessibility statement