Skip to main content

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

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:

 

 

 

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

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

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

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

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

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

 

 

 

 

 

 

 

  


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?

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

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 

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. 

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?

@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")

)

 

 

 

 

@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")

)

 

 

 

 


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 & time

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?

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

@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") )

 

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

Reply


Terms & Conditions