Created date time field and imported data

[Scott_Callery]

I have built an Airtable structure that utilizes the Created date/time field type to track when a record is added. This Airtable solution will replace an Excel version of the same data.

The Excel version contains 2 years worth of data with it’s own creation date column.
When I come to import this data into Airtable, the Created date/time field is going to return a value of the import date/time and rightfully, not the real creation date from Excel,

This means when I develop charts such as Records added by month, the imported data will always be linked to the import month. I will not be able to use the Created date/time field to analyse the historical data.

What is the best way to only have to use one creation field date (without the user manually choosing the creation date on a form) to track the actual creation date of both historical and current/future data?

[ScottWorld · Accepted solution]

Create 2 fields: one for the imported date/time, and one for Airtable’s built-in creation date/time.

Then, create a 3rd field: a formula field which checks if the imported date/time field is empty. If it’s empty, it should result in Airtable’s built-in creation date field. If it’s not empty, it should result in the imported date/time field.

Use this 3rd field for all of your future references to the creation date of the record.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

[Scott_Callery]

Perfect solution, thanks @ScottWorld!

[jord8on]

This worked for us as well. Thanks @ScottWorld 

Also thanks to @Kamille_Parks for her answer, including the formula, in another thread.

For anyone who, like me, finds this thread and is looking for a formula to help out, I've tweaked Kamille's work based on our needs:

We have three columns:

• {Airtable_Created_Time}: this is the standard Airtable "Created Time" field
• {Import_Created_Time}: a regular "Date" field where we imported the original date/time field from our CSV file
• {True_Created_Time}: a "formula" field where we added the following:

IF({Import_Created_Time}, {Import_Created_Time}, {Airtable_Created_Time})

But that gave us this result:

2023-01-27T07:14:06.000Z

So we changed the formula to this, which cleaned things up for us:

(DATETIME_FORMAT((IF({Import_Created_Time}, {Import_Created_Time}, {Airtable_Created_Time})),'YYYY-MM-DD @ hh:mma'))

2023-01-27 @ 07:14am

This was just what we needed!

I hope this helps someone else who needs a little extra info!

[Title3Funds]

having trouble with the script i need....We originally imported our dbase from another airtable (from a client)...that now should be getting updated into our table....which seems to be working yet the field i created (not sure i did it right) called "time created" seems to NOT always time stamp new data that feeds into this table from our client.  20 new records from yesterday only 3 show date/time stamp.  Suggestions?