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.
Solved
Convert Mmm-YY single line text field to its date field equivalent
+2Best answer by Jeremy_Oglesby
You can use the DATETIME_PARSE() function for this:
and pass it the proper format specifier
Your case would look like this:
DATETIME_PARSE({Your Text Date Field}, 'MMM-YY')
You can use the DATETIME_PARSE() function for this:
and pass it the proper format specifier
Your case would look like this:
DATETIME_PARSE({Your Text Date Field}, 'MMM-YY')
+2You can use the DATETIME_PARSE() function for this:
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.
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”:
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.
+2The 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.
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.
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.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.