How To Re-format Dates After CSV Import

[M_k]

Hi

I already have a formula for the date field, but I am noticing that after I do a CSV Import, for all those imported records, the date formula does not change the date format, even after changing the date field. I did not try to set up the formula field first, before importing records, I don’t know if that would work. The formula only works when I manually add new records, but I have 2,000 dates to format and I would like to not have to manually enter the dates again. What I would like to do is to beable to change the date format, in the date field, with the second field as the formula, without losing the dates when I change the date field from short text to date field. My understanding is that all the fields, once a CSV file is imported, are formatted to text fields. I am wondering if there is a way of keeping the dates formatted, once I change from text field to date field, even if I have to copy into another program or spreadsheet, the whole date column, and paste back into Airtable, or if there is another workaround or way of doing this.

Hope this makes sense.

Thanks,
MK

[Kamille_Parks]

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)?

[M_k]

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

[M_k]

Hi

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

Thank you,
MK

[drassi]

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.

[Kamille_Parks]

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.

[M_k]

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

[Kamille_Parks]

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
   

[M_k]

Hi Kamille_Parks

Thank you for your reply.

I will give this a try.

MK

[M_k]

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:

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

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.