Help changing single-select to date

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:

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

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

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

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

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.

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

Thanks for the tip, Jeremy!

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”?

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.

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.