Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Using both created time field + date field for two sets of data

Topic Labels: Dates & Timezones
Solved
Jump to Solution
349 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi,

I created a form for simple data entry and am including a “Created time” field so users will not have to manually fill in the date the form was submitted. The issue is, I also have legacy data from an old system that I want to import into the same table as the form submissions so the old data and any new data can be analyzed as a whole. This old data is organized by date and those dates are important to retain. When I add any old data into the table, the Created time field auto-fills to today’s date and I am unable to change it to the correct date.

Are there any solutions for maintaining the Created time field, but also maintaining the correct dates for the old data? The only option I’ve thought of is converting the Created time field to a Date field, but that would mean anyone submitting new data via the form would have to manually enter the date.

Thanks in advance!
Colleen

1 Solution

Accepted Solutions

You need 3 fields:

  • {Created Time}: the one you already have,
  • {Legacy Created Time}: a regular date field into which you will map your imported data to, and
  • {True Created Time}: a formula that outputs IF({Legacy Created Time}, {Legacy Created Time}, {Created Time})

See Solution in Thread

2 Replies 2

You need 3 fields:

  • {Created Time}: the one you already have,
  • {Legacy Created Time}: a regular date field into which you will map your imported data to, and
  • {True Created Time}: a formula that outputs IF({Legacy Created Time}, {Legacy Created Time}, {Created Time})
jord8on
6 - Interface Innovator
6 - Interface Innovator

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 and customized 

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!