Skip to main content

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!

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"

)

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"

))

Reply


Terms & Conditions