Help

Help changing single-select to date

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

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:


Thanks for your help!
V

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:
image
Thanks!

@Valida_Beckman,

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):
image
Which returned the date I wanted, plus the time stamp.
So then I reformatted it and unselected the “Include a time field option”:
image
Which returned the result I wanted:
image
Thanks again for helping me figure this out!
All the best,
Valida

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:

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