Help

Combined Date & Duration field into a datetime field

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
1136 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

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Got it @David_ALLEN-HUG  you might need to do a little wizardry to work around this. One thought that comes to mind is adding a single select field where the user specifies "All Day Event" vs "Non-All Day Event" (feel free to come up with better names), and then use a formula field with a conditional IF statement, to output the date (calculated in the earlier step) in the correct format depending on the event type. 

I was afraid you might say something like that! 😉

This is way above my ability. I have a checkbox field "All Day Event".  Any suggestions for formula I could use?

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

@David_ALLEN-HUG give this a go (change to match your field names):

IF(
  ({All Day Event?}), 
  DATETIME_FORMAT({Start}, "DD/MM/YYYY"),      
  DATETIME_FORMAT({Start}, "DD/MM/YYYY HH:mm")
)

 

Screenshot 2023-11-12 121206.png

 

Screenshot 2023-11-12 121108.png

So close!

FORMULA USED:

IF( ({All Day Event}), DATETIME_FORMAT({Start Date}, "MM/DD/YYYY"), DATETIME_FORMAT({Start Time}, "MM/DD/YYYY HH:mm") )


1. It shows error if All Day event is NOT checked
2. I think All Day event is mixed up...if box is NOT checked, then show the date & timeScreenshot 2023-11-12 at 9.18.40 AM.pngScreenshot 2023-11-12 at 9.18.36 AM.png

Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @David_ALLEN-HUG check the setup carefully, instead of running the formula on Start Date, it should run on the field that combines date and time together. Re look at my example and how I've setup my table, I've got the Date Field, then Start Time,  then 'Start' (which is the combined field). My formula in the 'Formatted Date' field, uses the 'Start' field. Where is the field on your table that combines date and time?

Okay...90% there!  The only issue remaining is when I UNcheck the All Day box, it gives an error.  REALLY appreciate your help!!
 
---
IF( ({All Day Event}), DATETIME_FORMAT({Combined START Date & Time}, "MM/DD/YYYY"), DATETIME_FORMAT({Start Time}, "MM/DD/YYYY HH:mm") )
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

@David_ALLEN-HUG just need a tiny change to use the combined field for both:

IF( ({All Day Event}), DATETIME_FORMAT({Combined START Date & Time}, "MM/DD/YYYY"), DATETIME_FORMAT({Combined START Date & Time}, "MM/DD/YYYY HH:mm") )

 

David_Allen-Hu1
6 - Interface Innovator
6 - Interface Innovator
Ok ... 99% of the way there! 
The combined date & time formula is showing a different time zone it seems?  I want it to be Los Angeles.

COMBINED DATE & TIME FORMULA:
DATEADD
({Start Date}, {Start Time}, 'seconds')
 
Screenshot 2023-11-12 at 3.40.08 PM.png