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?

[Play_At_pool]

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

[Justin_Barrett]

Play_At_pool:

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.

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.

[Play_At_pool]

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