Correctly Pasting Date and Time from Excel

[Joel_R_Putnam]

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]

Try this process and see if it works for you:

1. 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

2. Copy your “Date” and “Time” columns from Excel into those Airtable fields - they should retain their formatting, looking like dates and times

3. 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)

4. In the formatting section of that formula field, set the formatting to include both Date and Time in the local format

5. 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]

Jeremy_Oglesby:

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

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]

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

Airtable - Support

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]

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]

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]

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]

Joel_R_Putnam:

I’m looking for an entry in a date/time field that has a date, but no time.

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]

Jeremy_Oglesby:

The calendar view will allow you to display both “sets” of dates in the same calendar.

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]

Ah, yep - it’s an advanced feature.