Help

Combined Date & Duration field into a datetime field

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
2363 17
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Allen-Hu1
6 - Interface Innovator
6 - Interface Innovator

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.

Screenshot 2023-11-11 at 4.28.31 PM.png

 

Screenshot 2023-11-11 at 4.30.35 PM.png


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) & ''
)
)
1 Solution

Accepted Solutions
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Screenshot 2023-11-11 194931.png

 

Screenshot 2023-11-11 194916.png

 

See Solution in Thread

17 Replies 17
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Screenshot 2023-11-11 194931.png

 

Screenshot 2023-11-11 194916.png

 

David_Allen-Hu1
6 - Interface Innovator
6 - Interface Innovator

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

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
6 - Interface Innovator
6 - Interface Innovator

@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?

Screenshot 2023-11-11 at 10.26.10 PM.png

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

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

 

Screenshot 2023-11-12 072348.png

 

Screenshot 2023-11-12 072401.png

 

Screenshot 2023-11-12 072421.png

 

 

  

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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

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

IMG_0404.jpeg