Combined Date & Duration field into a datetime field

[David_Allen-Hu1]

We have a Start Date (Date field) and a Start Time (Duration Field).  I want to combine them together.

The first formula I have does combine them, but not in the way that airtable calendar view recognizes it as a date/time field.

Can you help me create a formula that will format it  properly (more like the "copy" field below, but showing the correct time.

Currently I am using:

DATETIME_FORMAT({Start Date},'M/D/YYYY')&' '&CONCATENATE(

FLOOR({Start Time} / 3600) & '',

':',

IF(MOD({Start Time}, 3600) / 60 < 10,

'0' & ROUND(MOD({Start Time}, 3600) / 60, 0),

ROUND(MOD({Start Time}, 3600) / 60, 0) & ''

)

)

[Arthur_Tutt · Accepted solution]

Hey @David_Allen-Hu1 great question and I feel like you're close. To combine a date and a duration and keep it in the correct Date Time format, use the DATEADD function and use 'seconds' as the time duration. Like this:

 

[David_Allen-Hu1]

@Arthur_Tutt for the win!  Thank you for your super speedy and helpful response!

[David_Allen-Hu1]

Ack - got another related issued.  
I duplicated concept for End Date & End Time...but many events do not have an end date and/or time.  How can I adjust this to not show if EITHER end date or End time is missing?

How does this impact "multi day events" such as "Off for Thanksgiving week"?

DATEADD({End Date}, {End Time}, 'seconds')

[David_Allen-Hu1]

@Arthur_Tutt - one more issue!

The formula is having the combined startdate/time field showing a different time zone (GMT?). We are Los Angeles time (with daylight savings). Any help with this and/or above?

DATEADD({Start Date}, {Start Time}, 'seconds')

[Databaser]

IF(AND({End Date}, {End Time}), DATEADD({End Date}, {End Time}, 'seconds')) will only give you an output if both fields have a value. 

[Arthur_Tutt]

@David_Allen-Hu1 Airtable seems to have a weird quirk where a date on it's own (with no time stamp) is hard set to GMT. You can see this by showing the time zone on your date field. A simple (but not perfect) work around is to show the time on your date field. That forces Airtable to pull in the correct time zone. 

 

 

  

[David_ALLEN-HUG]

Ugh. This is what I originally did but then separated the fields so we could better manage “all day” events.

if I put them back into one date/time field, how do I get airtable to properly display the “all day” events?

[Arthur_Tutt]

Hey @David_ALLEN-HUG  when you say display all day events what do you mean? what kind of output are you looking for? 

[David_ALLEN-HUG]

 Where the all day event isn’t linked to a specific time and shows up at the top, like these birthday ones in attached