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!

[Jeremy_Oglesby]

Can you clarify what you are trying to do? Are you trying to take the value of a Single select field and convert it, via a Formula, to a Date value?

[Valida_Beckman]

Yes, I’m trying to take an Airtable Single-select field (for month) I’ve integrated from JotForm and have it reflect as M/DD/YYYY in another Airtable column.

Right now I’m getting an error value when using the DATETIME_PARSE() formula.

I’ve tried it a couple different ways – once having the date column configured as a Date format, and the other configured as a Single-line text format.

Could the integration be the source of the issue?

Would it help if I have the integrated field then formulated to an additional Airtable column that’s Single line text?

Then do the formula off that column instead?

The article addresses formulating off a Single-line text field, instead of a Single-select field.

Thanks for your help!

[Jeremy_Oglesby]

No, there shouldn’t be any need to convert to a Text field first – Single select fields come through as text when you utilize them in a formula.

Valida_Beckman:

Single-select field (for month)

So this field is only a month? How is it formatted - name, number, abbreviated name? When you convert it to a date, what do you want the Day and Year to be when you parse a date out of it? Are there different fields that supply the Day and Year?

[Valida_Beckman]

It’s formatted as a name (i.e., “January”).
And I want it to be MM/YYYY actually (not M/DD/YYYY) - so to show “01/2020”, to use my example. I just changed the formula from M/DD/YYYY to MM/YYYY, and it’s still returning the error.
Here’s a screenshot of the format of the selections in the Single-select field:

Thanks!

[Nathalie_Collin]

@Valida_Beckman,

Where is the year information going to come from? Do you have another field to pull in from?

[Valida_Beckman]

Whoa, I just figured it out…
So I had to make the end format match the format of the source. And I read somewhere that to identify a full-name month in a formula, you need to use ‘MMMM’.
So I fixed that in the formula (1st screenshot):

Which returned the date I wanted, plus the time stamp.
So then I reformatted it and unselected the “Include a time field option”:

Which returned the result I wanted:

Thanks again for helping me figure this out!
All the best,
Valida

[Jeremy_Oglesby]

Valida_Beckman:

I want it to be MM/YYYY actually (not M/DD/YYYY) - so to show “01/2020”,

Doesn’t look like you are getting what you want out of that though. If you truly just want “M/YYYY”, you should be able to get that with this:

MONTH(DATETIME_PARSE({Field 16}, 'MMMM')) & "/" & YEAR(DATETIME_PARSE({Field 16}, 'MMMM'))

[Valida_Beckman]

Yes indeed, that would be ideal. Thanks for sharing that – I found a similar formula in the community before, but it didn’t have the / in quotation marks. There was just a space between them. I would recommend trying to find that article and adding a correction, so other folks don’t go down that same rabbit hole!

[Jeremy_Oglesby]

This will get you the leading 0 on the month if it’s a single digit month:

REPT(
   '0',
   2 - LEN(MONTH(DATETIME_PARSE({Field 16}, 'MMMM'))&'')
) & 
MONTH(
   DATETIME_PARSE({Field 16}, 'MMMM')) &
   "/" &
   YEAR(DATETIME_PARSE({Field 16}, 'MMMM')
)