Date and Time import from google sheets

[Sara_Sierschula]

My first post! This community has been very helpful. I’m still fairly new to airtable and hope someone can help with this problem.

I have a Google Sheet with a date field, a start time field, and a stop time field. I want to import it into a table with 2 fields: {date and start time} and {stop time}.

I tried to join fields in google sheets, then upload, but it only uploads the date correctly, and the time is ‘12:00am’. I’ve tried different date/time formats, but that hasn’t worked, and made the problem worse in some cases. For example it only works with the 12 hr format, when I tried 24 hour, neither the date nor time uploaded.

Any help or suggestions would be greatly appreciated!

[Sara_Sierschula · Accepted solution]

I got help from Airtable - here is the format:

ID, Date
1, 2020-03-04T10:20:00.000
2, 2020-02-01T20:15:00.000
3, 2020-04-30T01:00:00.000

I did a join formula in google using ‘T’ as the delimiter.

[AutoMattic]

Surprisingly, as of June 2024, I've found that the above isn't working.   I started searching for this issue after I was having trouble copy and pasting from Google Sheets.  I had a date field and a time field and I was using a formula to combine them into 1 field and using the field format to set the format similar to AirTable.  I was having trouble copy/pasting.

So, then I started searching google + AirTable forums for this issue and came upon this article.  I tried repeatedly to follow the above, also with slight variations to the date/time formats and nothing worked.  When I copy and paste from Google Sheets to AT date/time cell, it ends up blank.   If I copy a date only field in Sheets and paste into date/time field in AT, the date comes in perfectly and the time is of course, 12:00am.

I'm not sure what format I started with that didn't work in Sheets before trying the solution above with the "T", but after more experimentation, in both Excel and Sheets now, oddly enough, formatting a cell in Sheets and Excel that looks like this (I'm west coast US for timezone/format): 9/26/24 10:30AM (sheets) and 10/27/24 9:00am (Excel), both will now paste into an AirTable date/time field correctly.

So, no "T" is required between the date and time and doesn't work at all.  But, instead a " " blank space) in between date/time does work.

I'm not sure what my original problem was now with the format, as what is working now seems to be the most straight forward date/time field in Sheets that I would have started with.

I also tried adjusting the field's date format and time zone settings (when you edit the date/time field in AT).  For date format, I had Local set and I changed that to US.  Both those settings work with the pasting now...so no difference there.  And while I was having issues and now that I've resolved this, I've always had the timezone set to Los Angeles and same time zone used for all collaborators (we don't have users in other timezones in our org).  I hope this helps other who may come across these copy/paste issues.  They are always tricky betweens spreadsheets and databases of all kinds.  Good luck!

-Matt