Help changing single-select to date

Topic Labels: Formulas
2394 14
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

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:

Thanks for your help!

Let me know if that helps!

14 Replies 14

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?

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!

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.

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?

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:


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

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,

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

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!

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

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