Using DATEADD formula to create Datetime from Date and Time columns


#1

I’ve recently started using Airtable to cut down on the number of spreadsheets that don’t talk to each other, and loving the improvements we have made so far.

One of the struggles was not being able to use a lot of features due to dates and times being in seperate columns in all our other documents, and wasn’t able to find other community posts solving this in a simple way.

After a few weeks of messing about, I finally found the DATEADD formula is the simplest:

DATEADD({Date},{Start Time},‘seconds’)

I use a similar formula to calculate the end times.

I can now use all the lovely DATETIME formulas I’ve been excited to try out without having to do manual input, which was the goal all along.

Has anyone else had to discover this themselves? Any other workarounds people have created?


#2

Nicely creative workaround!

Here are a couple of others based on DATETIME_FORMAT() and DATETIME_PARSE(). Note the format specifier I use assumes your {Date} is in the format ‘M/D/YYYY’ and {Start Time} is a 12-hour clock with ‘am’ or ‘pm’ in lower case: e.g., ‘7:34 am’. Depending on how your variables are formatted, you might need to use a slightly different version.

If your {Date} and {Start Time} are both text strings

DATETIME_PARSE(
    {Date}&' '&{Start Time},
    'M/D/YYYY h:mm a'
    )

If {Date} is a date field and {Start Time} is a text string

DATETIME_PARSE(
    DATETIME_FORMAT(
        {Date},
        'M/D/YYYY'
        )&' '&{Start Time},
    'M/D/YYYY h:mm a'
    )

#3

Thanks!

The reference to Datetime_Format and Parse is useful, and you may be able to link me elsewhere for this question - I’m trying to use the resulting Datetime column to define the date field of a calendar, but it’s defaulting to UTC, rather than my local time zone here in Australia.

The SET_TIMEZONE function only works with DATETIME_FORMAT, the result of which isn’t a valid field type for the calendar.

Setting the field to ‘Use same time zone for all collaborators’ isn’t ideal when using the iCal export from the Calendar view, since even though it looks right inside Airtable it’ll do the conversion in Outlook/Google Calendar etc.


#4

@Andy_Lin1 provided what is probably the most thorough dissection of SET_TIMEZONE() ro date. My reply — the second post in the trad — offers a good, easy work-aoround for getting a datetime intoi local time, something that appears necessary when using DATETIME_PARSE().


#5

Thanks so much!

That’s an excellent explanation, and his example of having multiple time zone conversions in one table is exactly what I’m doing, so it’s nice to have a step-by-step guide.

I am however having a problem with Airtable incorrectly calculating half-hour time differences like ‘Australia/Adelaide’ (UTC+9.5).

I posted there, but given the age of the post may not get a response - any ideas?