Skip to main content

Generate Date based on Month input

Z

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?

3 replies

Kamille_Parks11
Forum|alt.badge.img+25

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

DATETIME_PARSE(
   YEAR(TODAY()) & "/" & {Month} & "/01", 
   "YYYY/MMMM/DD"
)
Justin_Barrett
1 person likes this
  • Justin_Barrett
    Justin_Barrett
Z
  • Zarah_Eapen
  • Author
  • New Participant
  • 1 reply
  • November 24, 2021

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_Parks11
Forum|alt.badge.img+25
Zarah_Eapen wrote:

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?


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)

Z
Justin_Barrett
2 people like this
  • Z
    Zarah_Eapen
  • Justin_Barrett
    Justin_Barrett

Reply


Most helpful members this week

Terms & Conditions