Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Convert Mmm-YY single line text field to its date field equivalent

Solved
Jump to Solution
1822 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Brent_Hayes
4 - Data Explorer
4 - Data Explorer

what is the simplest way to convert a text field which is the three letter month '-'two letter year (Dec-02, May-18, etc) to a usable date field of the same format. The day of the month isn’t important, could be set to 01.

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

You can use the DATETIME_PARSE() function for this:

CleanShot 2020-08-17 at 09.27.48

and pass it the proper format specifier



Your case would look like this:

DATETIME_PARSE({Your Text Date Field}, 'MMM-YY')

See Solution in Thread

5 Replies 5
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

You can use the DATETIME_PARSE() function for this:

CleanShot 2020-08-17 at 09.27.48

and pass it the proper format specifier



Your case would look like this:

DATETIME_PARSE({Your Text Date Field}, 'MMM-YY')

Thank you. I’m new to Airtable. How do i now convert this formula field to an actual date? In the Excel world i would simply Copy and then Paste Values.

The formula field itself can be formatted as a “Date” field. If you click the dropdown menu for the field and select “Customize field type”:
CleanShot 2020-08-18 at 09.05.10

And then select the “Formatting” tab, you’ll see that it should be already formatted as a Date field:
CleanShot 2020-08-18 at 09.06.32

If that doesn’t answer your question (and I have a feeling it doesn’t), then I’m not sure I understand what you are asking.

Thank you for your patience.
What i’m trying to do is take the current single line text field MMM-YY.
Convert it to it’s date field equivalent so i can do sorts and filters and date calculations.
Be able to enter manually enter or choose a new date in the same field.
Then have the date field still display the format MMM-YY as a date type field.

Ah, so you want the field that holds the final date to be editable? Do you want the field that holds the final date to be able to have a different date than the field holding the text ‘MMM-YY’ text date, by manually changing it?

If that’s what you are after, I think you will need a third field - a field of type “Date field” that you can use to manually override, or change the date that ends up in the formula field. So you’ll have 3 fields total in your setup:

{Text Date Field}   |   {Manual Override Date Field}   |   {Formula Field}
----------------------------------------------------------------------------------
'Dec-20'            |   11/2/2020                      |   11/2/2020
'Dec-20'            |                                  |   12/1/2020

If you enter a date in the {Manual Override Date Field}, it will display in the {Formula Field} instead of the converted value from the {Text Date Field}.

The {Manual Override Date Field} would be a Date field - the normal kind where you manually pick or enter a date.

The {Formula Field} would now need this formula:

IF(
   {Manual Override Date Field},
   {Manual Override Date Field},
   DATETIME_PARSE({Your Text Date Field}, 'MMM-YY')
)

There are other, more complicated ways to achieve this as well, if this doesn’t suit your needs… but I’d give this a try first.