How To Re-format Dates After CSV Import

Have you tried duplicating your Formula Field after you do the import? What is the formula you’re using? What do your imported dates look like (i.e. Feb 6, 2018, 02/06/18, etc)?

Hi Kamille_Parks

Yes, I tried to create the formula field after I imported the CSV file, but all that it did was it replicated the date format from the original date field, but when I try to format the text field to Date field, then all of the dates disappear, eventhough I setup the formula field, it did not help.

I have a screenshot of how the dates are formatted when I created the date field formula, after import:

When I manually enter a new record, in the date field, after I did a CSV Import, this is how it looks (screenshot):

This is the date formula that I am using, I am not so good with creating formulas, so if I need a new one, I will need to know exactly how it should be written:

DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY’)

Thank you for any help.

MK

Hi

Does someone have a suggestion for above posts? I sure could use a solution to this question

Thank you,
MK

I’m not 100% clear on the question you’re asking—though I think you are trying to ask, how to turn one of your text or formula fields, into a date field?

I’ve always been able to do this by converting text or formula fields that are formatted in the “L” format (MM/DD/YYYY). Have you tried that?

e.g.

I see what’s happening. Your Date Issued field has the format ‘ddd, MMMM D, YYYY’ for imported fields but for your manual new records you’re imputing dates using the format ‘MM-DD-YYY’

You’re telling AirTable you want the end result to be “Wed, January 10, 2018” by using the formula you’ve given it. Since your imported entries are already like that, AirTable isn’t going to do anything for those records.

Make a second formula field using DATETIME_FORMAT({DATE ISSUED},‘MM-DD-YYY’)
^ and then copy the values for all your imported CSV entries into your original “Date Issued” field. The formula field you’re using now will then produce the result you’re after. After you do this you can delete your second formula field, and convert “Date Issued” into a date field.

Hi Kamille_Parks

I tried your suggestion, but the formula didn’t work, but I adjusted it and I managed to format the dates in the second formula field. I am still having the dates disappear. I then contacted Airtable support and this was their reply, but I don’t quite know how to do it and I would like to beable to get the field formatted.

This was the reply that I got from Airtable:

“In a case like this, you’re on the right track. You will need to use a formula field to format the dates in such a way that you can then change the field to a date field (or reference the field with a datetime_parse() formula function).”

The formula that I got to work is this:

DATETIME_FORMAT({DATE ISSUED 2},‘MMMM D, YYYY’)

I would really like to get this working, since I have 2,000 dates that I have to change.

Thank you,
MK

Do the following. If this doesn’t work, you’ll have to share a view of your table so we can see what the issue is. I started from an imported csv and then added three dates to try and match your scenario. My dates don’t disappear when I do this:

1. Make a formula field called {Date Issued: Fixed} with the formula DATETIME_FORMAT({Date Issued},'MM/DD/YYYY')

2. Make another formula field called {Date Issued: Pretty} with the formula DATETIME_FORMAT({Date Issued},'ddd, MMMM D, YYYY') or whatever you want your display format to be.

3. Convert {Date Issued: Fixed} into a date field
   

4. Select all of your cells for {Date Issued: Fixed} and paste them into your original Date Issued field. This should automatically convert Date Issued into a date field type. Delete Date Issued: Fixed
   

Hi Kamille_Parks

Thank you for your reply.

I will give this a try.

MK

Hi

i wondered if someone can help me with this issue.

I managed to get this to work, but instead of “DATE ISSUED” field format staying the same (Wed., March 13, 2018) it got converted to 03-13-2018, when I changed the field format to “DATE” field, after following the instructions in Reply 9. When I followed step 3, I selected “Friendly Date” format, instead of the default date (it works with the default date, but not the “Friendly Date” format date) that was when I lost all the dates in the field. I also tried reversing the formulas for steps 1 and 2, but when I changed to “Friendly Date” format date, that’s when I lost all the dates, again.

I have included a link to a copy of the Airtable base:

What I what to do is convert the “DATE ISSUED” field from a “TEXT” field to a “DATE” field, keeping the date format to:

Wed., March 13, 2018

Using this formula (“FORMULA” field, beside “DATE ISSUED” field):

DATETIME_FORMAT({DATE ISSUED},‘ddd, MMMM D, YYYY’)

Thank you,
Mary

P.S. The date format in the “DATE ISSUED” field was the same format that was from the original source, before import.

Hi

Could someone take a look at this?

I am not sure if I could achieve the date format I want showing in the “DATE ISSUED” field or if it can be formatted to the date format I want or even Friendly format. Otherwise, I will just use the default date format.

I tried to use the Friendly date format when I followed the steps, but the field went blank, but when I choose the default date format, when following the steps, it works.

If anyone can help, it would be appreciated.

Thank you,
Mary

To convert from text to date, you’ll want to use DATETIME_PARSE rather than DATETIME_FORMAT. The format specifier remains the same.

Hi W_Vann_Hall

Thank you for your reply.

Would I keep the rest of the formula the same?

Thank you,
Mary

Yes, the general structure of the functions is the same:

FUNCTION({Field},'Specifier')

As far as I recall, DATETIME_PARSE is forgiving as far as non-specifier characters go, so I think you can use the formula as given; if it gives you any trouble, though, you might want to try

DATETIME_FORMAT({DATE ISSUED},'ddd., MMMM D, YYYY')

to account for the period after the day-of-the-week abbreviation.

FUNCTION({Field},'Specifier')

DATETIME_FORMAT({DATE ISSUED},'ddd., MMMM D, YYYY')

Hi W_Vann_Hall

Thank you.

Helpful as ever.

Mary