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

[Brent_Hayes]

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.

[Jeremy_Oglesby · Accepted solution]

You can use the DATETIME_PARSE() function for this:

and pass it the proper format specifier

Airtable - Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

---

---

Your case would look like this:

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

[Brent_Hayes]

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.

[Jeremy_Oglesby]

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

And then select the “Formatting” tab, you’ll see that it should be already formatted as a Date field:

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.

[Brent_Hayes]

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.

[Jeremy_Oglesby]

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.