Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 17, 2020 09:21 AM
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! Go to Solution.
Aug 17, 2020 09:29 AM
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')
Aug 17, 2020 09:29 AM
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')
Aug 18, 2020 07:05 AM
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.
Aug 18, 2020 09:07 AM
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.
Aug 19, 2020 07:40 AM
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.
Aug 19, 2020 10:19 AM
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.