How To Re-format Dates After CSV Import


#1

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


#2

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


#3

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


#4

Hi

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

Thank you,
MK


#5

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.




#6

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.