Generate Date based on Month input

[Zarah_Eapen]

Hi,

I am trying to generate a “Date” based on the month input available in my airtable. I tried using the If function, however, the result is not being recognised as a date.

A little more information on my use case. I’m creating a database from a form that asks people to enter their preferred month, these months are provided as dropdowns. So, for example, if my table has October as a Month - I want to be able to create an additional column that tells me the first date of the months, i.e October 1, 2021, should be the value. Any ways to do this?

[Kamille_Parks]

You can use DATETIME_PARSE() to assemble a date like so:

DATETIME_PARSE(
   YEAR(TODAY()) & "/" & {Month} & "/01", 
   "YYYY/MMMM/DD"
)

[Zarah_Eapen]

Thank you for the response, this works perfectly for my requirement. A quick follow on question. The results to the formula are apt to December but say for January, I’ll need it to reflect the year as 2022. How would you recommend altering the formula then?

[Kamille_Parks]

Use a DATEADD function with an IF() statement to determine if you need to add a year or not.

DATEADD(
   DATETIME_PARSE({Month}, "MMMM"), 
   IF(DATETIME_PARSE({Month}, "MMMM") > TODAY(), 0, 1), 
   "year"
)

(This formula uses a simplified syntax to the one above since declaring just a month appears to return the first day of that month for the current year. This may be less reliable than the original format shown since you’re explicitly declaring a year and day. If you need a more foolproof formula, substitute this formula’s DATETIME_PARSE format for the own shown above)