Using DATEADD formula to create Datetime from Date and Time columns

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'

    )

DATETIME_PARSE(

    {Date}&' '&{Start Time},

    'M/D/YYYY h:mm a'

    )

DATETIME_PARSE(

    DATETIME_FORMAT(

        {Date},

        'M/D/YYYY'

        )&' '&{Start Time},

    'M/D/YYYY h:mm a'

    )

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.

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

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?

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.

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?

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.

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

)

DATETIME_FORMAT(

    {Date start},

    'M/D/YYYY'

)

DATETIME_FORMAT(

    {Date start},

    'M/D/YYYY'

) & IF(formula, ' ' & formula)

DATETIME_PARSE(

    DATETIME_FORMAT(

        {Date start},

        'M/D/YYYY'

    ) & IF(formula, ' ' & formula),

    'M/D/YYYY' & IF(formula, ' H:mm')

)

DATETIME_PARSE(

    DATETIME_FORMAT(

        SET_TIMEZONE({Date start}, "TIMEZONE_SPECIFIER_HERE"),

        'M/D/YYYY'

    ) & IF(formula, ' ' & formula),

    'M/D/YYYY' & IF(formula, ' H:mm')

)

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… sorry for any inconvenient…

Thank you so much for your help

‘DD/MM/YYYY H:mm’)

The Datetime_parse format was the issue. Originally I had it as M/D/YYYY. I have changed it to DD/MM/YYYY, however, the result is MM/DD/YYYY. So i use airtable date formatting to change it back to DD/MM/YYYY. I am still confused with the time zone part so i haven’t done anything with that.

Thank you so so much for your help.

That may be due to the field formatting. Check the formatting tab on the formula field, and you’ll probably find that it’s on the default, not on European.

On a side note, you can greatly condense the time-extraction part of your formula. Because you’re always taking the first time from the time selection in {Class Time Booked}, you can extract the first five characters with the LEFT() function, then trim it (for shorter times 9:00 and 9:30, which will pick up the space after the number), and you’re done. Here’s the latest version above with that change applied:

DATETIME_PARSE(DATETIME_FORMAT({Start Date}, 'DD/MM/YYYY')&' '&TRIM(LEFT({Class Time Booked}, 5)), 'DD/MM/YYYY h:mm')

As I said above, you can (and should) drop the trailing “a” at the end of the parse format. You are using 24-hour time, so you don’t have an AM/PM indicator in your times, and therefore shouldn’t be telling the parser to look for one.

DATETIME_PARSE(DATETIME_FORMAT({Start Date}, 'DD/MM/YYYY')&' '&TRIM(LEFT({Class Time Booked}, 5)), 'DD/MM/YYYY h:mm')

Thank you so much i will trim it instead of if.