Help changing single-select to date

[Valida_Beckman]

Hello,
I asked for some help with a formula to convert a single-select field to a date field, and was sent a link - but unfortunately I cannot access it.
Can someone please provide a way to access the support article I was directed to?
The topic is how to use the DATETIME_PARSE formula to convert that to MM/YYYY . Here’s the support article link I was sent:

support.airtable.com

Airtable - Support

Thanks for your help!
V

Let me know if that helps!

[kuovonne]

It looks like your single select field includes only the month, not the year.
Thus, what do you want the year to be?

Do you want the year to always be the current year, so that when 2021 rolls around the year in the year in the formula field will change, or do you want the year to stay 2020?

If you want to keep the year as 2020, you will need to find a different source for your year, such as a created time for the record or a last modified time.

[Valida_Beckman]

Thanks for the tip, Jeremy!

[Valida_Beckman]

That’s a good point. Users will be using the JotForm to report for the prior month’s activity. So in January 2021, they’ll be reporting for December 2020.

If I kept as is, are you saying that when “December” gets integrated that the formula date field will show “12/1/2021” and not “12/1/2020”?

[Jeremy_Oglesby]

Yes - the formula I provided will always provide the current year at the time the form was filled out. So somebody filling out the form in January 2021, and selecting December as the month, will produce 12/2021, not 12/2020 as you are wanting.

What you are wanting is possible, but a bit more technically complicated. If I’m able to come up with something today, I’ll post back – I’m guessing someone else, like @kuovonne, can provide it before me, though, as I’ve got a busy day of work.

[kuovonne]

You could have a formula that calculates the year based on the created date for the record. The formula would have to calculate the year for the month prior to the month that the record was created. For example, if the record was created in January 2021, the formula need to calculate the previous month as December 2020 and use the year 2020.

However, this method is very brittle, and doesn’t account for people submitting information at some other time. For example, if someone submits info on the last day of the month because it is a weekend and they are getting ahead of the game. Or someone is a month late submitting info.

It is far better to add the four digit year to your JotForm to ensure that you are obtaining the correct year. Then you can use a formula that creates a date from the month and year:

DATETIME_PARSE({Month Field} & "/1/" & {Year Field}, 'MMMM/D/YYYY')

To turn the date object back into a string showing only the two digit month and the year:

DATETIME_FORMAT(
  DATETIME_PARSE({Month Field} & "/1/" & {Year Field}, 'MMMM/D/YYYY'),
  "MM/YYYY"
)

I put a writeup and a working demo on my website.