Correctly Pasting Date and Time from Excel

Joel_R_Putnam · ‎Jun 07, 2018

Hi there, I’m moving a spreadsheet to Airtable and I’m loving the extra functionality so far! Let’s just say many-to-many relationships are amazingly useful.

I’m having a surprising problem though. I’d like to paste a series of times and dates from Excel into Airtable. Right now I have the dates and times in two seperate columns, and not all records have a specific time (think of them as “all day” in google calendar land).

I’ve been able to combine the columns in excel, though not without adding a midnight time to the records without dates. Interestingly when I do this with the obvious formula (=A1+b1, etc), It first spits out either a five digit integer, or a number with five decimal places. For example, 7/22/14, 11am becomes 41842.45833. But if I convert the number format in Excel to date and time, it displays correctly.

The real weridness happens when I then try to paste that into a Date and time field in Airtable. That same date above turns into 6/14/0833 1:00am. Not so useful.

I’m hoping to copy in about 400 records, so I’d really rather not have to do this by hand. I also want to take advantage of calendar features so I’d rather not paste it all in as single line text. Any suggestions for how to get my date/time data from Excel to Airtable? Thanks!

Jeremy_Oglesby · ‎Jun 07, 2018

Try this process and see if it works for you:

Create two fields in Airtable, both single line text, to accept your “Date” and your “Time”, as you had it set up originally in Excel
Copy your “Date” and “Time” columns from Excel into those Airtable fields - they should retain their formatting, looking like dates and times
Create a third field in Airtable, a Formula field with this formula in it:

DATETIME_PARSE(
   {Date} & “ “ & {Time},
   ‘D/M/YYY ha’
)

(This assumes all of your times are formatted “11am”, “1pm”, “10pm”, “8am” as you have in your example - if that’s not the case respond back with how your times are formatted)

In the formatting section of that formula field, set the formatting to include both Date and Time in the local format
If that works, and is returning dates, try duplicating that formula field, and then on the duplicate, change the field type to “Date” and set it to show both Date and Time in the local format

That may get you where you need to go - if so, you can delete all the fields except that last one which you converted to a Date field.

Joel_R_Putnam · ‎Jun 08, 2018

Thanks! It’s halfway working because my times are actually formatted slightly differently. I’ve been playing with the formula but can’t quite figure it out. The current format of the date is MM/DD/YYYY and the time is H:mm A, so for example “7/22/2014” and “11:00 AM”

Jeremy_Oglesby · ‎Jun 08, 2018

Here’s a reference to help you figure out how you need to format the DATETIME_PARSE function:

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

Joel_R_Putnam · ‎Jun 08, 2018

Got it just as you posted that! The correct formula in my case turned out to be

DATETIME_PARSE({Date} & " " & {Time}, ‘MM/DD/YYYY H:mm A’)

Now, this is likely a feature request instead of a how-to question, but, for bonus points, can we have just a EDIT: date in a date/time column where there is no time specified?

Jeremy_Oglesby · ‎Jun 08, 2018

No - a DateTime field always has to have a date for the time to be associated with.

I’m not sure what your application or need is there, but you could always just enter a separate time in a Single-Line Text field and perform conversions on it when needed to interact with other fields…?

Joel_R_Putnam · ‎Jun 08, 2018

The point would be to make all day events in calendar view, or to have something where the date is not yet known.

EDIT: just realized I’d made a critical mistake in my previous post. I’m looking for an entry in a date/time field that has a date, but no time.

Jeremy_Oglesby · ‎Jun 08, 2018

In that case, yes - in the “Customize Field” options for Date fields, you can uncheck the option to include a time. This should emulate the concept of an “All Day Event” in the calendar. If you need to combine this with timed events in the same calendar, there may be a couple ways you could do that.

One option (probably the simplest) would be to just have separate start time and end time fields (in addition to the date only field). For timed events, you’d use the start and end fields, for all day events you’d use the date field. The calendar view will allow you to display both “sets” of dates in the same calendar.

Joel_R_Putnam · ‎Jun 14, 2018

I’m not seeing that ability, but it’s possible that it’s a pro-only feature - I’m a free user. I do see something about multiple date fields being available to pro users.

Jeremy_Oglesby · ‎Jun 14, 2018

Ah, yep - it’s an advanced feature.