Help

IF statement to return specific date

Topic Labels: Dates & Timezones
1094 2
cancel
Showing results for 
Search instead for 
Did you mean: 
LaurenAdrian_Pr
4 - Data Explorer
4 - Data Explorer

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 2

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

Screenshot 2022-09-20 at 11.14.41 AM

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"
))