Using DATEADD formula to create Datetime from Date and Time columns

[Josh_Haywood]

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?

[W_Vann_Hall]

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'
    )

[Josh_Haywood]

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.

[W_Vann_Hall]

@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().

[Josh_Haywood]

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

SET_TIMEZONE and other time-related quirks

I have a problem that the conversion doesn’t allow timezones in half-hour increments, like ‘Australia/Adelaide’ (UTC+9.5). It’s instead rounding up to a time difference of 10 hours.

Is this a bug with Airtable or is there something I can do with my formulas to get it to display accurately? I’m using the conversion method pretty much exactly as you have when handling different timezones in one table.

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

[Play_At_pool]

I have been trying to add a date and a time together. As suggested my Date is a date field and my time is a formular feild. The problem i am having is some of the field provide a correct result and some give out #error!! What should be the cause of this.

[Justin_Barrett]

Welcome to the community, @Play_At_pool! :grinning_face_with_big_eyes: Can you share the formulas you’re using for both the time field and field where you’re trying to merge the time with the date?

[Play_At_pool]

This is the formular i am trying to use.
DATETIME_PARSE(
DATETIME_FORMAT(
{Date start},
‘M/D/YYYY’
)&’ '&if(formula),
‘M/D/YYYY h:mm a’
)

I dont have my notebook with me can access from my mobile
The {Date Start} field is input does not include time.
This field is the date which student start there course.
The if formula field give out a time a class start, which give out a time 9:00 , 10,30 or 14:00. This field is the time slot the student choose to attend the class.
I am trying to get the date and time to create a calendar when each student supposed to attend a class.

[Justin_Barrett]

Thanks for sharing that! It looks like your formula is malformatted and incomplete. Let’s start from the ground up. First, let’s format your existing date:

DATETIME_FORMAT(
    {Date start},
    'M/D/YYYY'
)

If the formula outputs a time, add that onto the end with a separating space before it:

DATETIME_FORMAT(
    {Date start},
    'M/D/YYYY'
) & IF(formula, ' ' & formula)

Now we need to parse that date, but the parsing parameters also need to change depending on the output of your other formula. The base format “M/D/YYYY” will always be there, though, so we can start with that, and only add the time portion if the formula has output anything. (I changed “h” to “H” for the formatting of the hour, and also removed the trailing “a” because your example used 24-hour time vs 12-hour time.)

With that added, the final formula is this:

DATETIME_PARSE(
    DATETIME_FORMAT(
        {Date start},
        'M/D/YYYY'
    ) & IF(formula, ' ' & formula),
    'M/D/YYYY' & IF(formula, ' H:mm')
)

One last thing to note: you might need to force the timezone to your local timezone when formatting the date. If so, it would look like this (make sure to insert your own timezone option, using the examples provided here.)

DATETIME_PARSE(
    DATETIME_FORMAT(
        SET_TIMEZONE({Date start}, "TIMEZONE_SPECIFIER_HERE"),
        'M/D/YYYY'
    ) & IF(formula, ' ' & formula),
    'M/D/YYYY' & IF(formula, ' H:mm')
)

[Play_At_pool]

IF({Class Time Booked}=“9:00 - 9:30”,“9:00”,IF({Class Time Booked}=“9:30 - 10:00”,“9:30”,IF({Class Time Booked}=“10:00 - 10:30”,“10:00”,IF({Class Time Booked}=“10:30 - 11:00”,“10:30”,IF({Class Time Booked}=“11:00 - 11:30”,“11:00”,IF({Class Time Booked}=“11:30 - 12:00”,“11:30”,IF({Class Time Booked}=“12:00 - 12:30”,“12:00”,IF({Class Time Booked}=“12:30 - 13:00”,“12:30”,IF({Class Time Booked}=“13:00 - 13:30”,“13:00”,IF({Class Time Booked}=“13:30 - 14:00”,“13:30”,IF({Class Time Booked}=“14:00 - 14:30”,“14:00”,IF({Class Time Booked}=“14:30 - 15:00”,“14:30”,IF({Class Time Booked}=“15:00 - 15:30”,“15:00”,IF({Class Time Booked}=“15:30 - 16:00”,“15:30”,IF({Class Time Booked}=“16:00 - 16:30”,“16:00”,IF({Class Time Booked}=“16:30 - 17:00”,“16:30”,IF({Class Time Booked}=“17:00 - 17:30”,“17:00”)))))))))))))))))

This is my Time formula
Which convert a range of time of the class 9:00-9:30 output 9:00 , 9:30-10:00 output 10:00 etc
With the above formula
i added
DATETIME_PARSE(DATETIME_FORMAT({Start Date},‘DD/MM/YYYY’)&’’&IF({Class Time Booked}=“9:00 - 9:30”,“9:00”,IF({Class Time Booked}=“9:30 - 10:00”,“9:30”,IF({Class Time Booked}=“10:00 - 10:30”,“10:00”,IF({Class Time Booked}=“10:30 - 11:00”,“10:30”,IF({Class Time Booked}=“11:00 - 11:30”,“11:00”,IF({Class Time Booked}=“11:30 - 12:00”,“11:30”,IF({Class Time Booked}=“12:00 - 12:30”,“12:00”,IF({Class Time Booked}=“12:30 - 13:00”,“12:30”,IF({Class Time Booked}=“13:00 - 13:30”,“13:00”,IF({Class Time Booked}=“13:30 - 14:00”,“13:30”,IF({Class Time Booked}=“14:00 - 14:30”,“14:00”,IF({Class Time Booked}=“14:30 - 15:00”,“14:30”,IF({Class Time Booked}=“15:00 - 15:30”,“15:00”,IF({Class Time Booked}=“15:30 - 16:00”,“15:30”,IF({Class Time Booked}=“16:00 - 16:30”,“16:00”,IF({Class Time Booked}=“16:30 - 17:00”,“16:30”,IF({Class Time Booked}=“17:00 - 17:30”,“17:00”))))))))))))))))),‘M/D/YYYY h:mm a’)

I am not sure how to attach my file … i am very low tech person… :slightly_smiling_face: sorry for any inconvenient…
Thank you so much for your help