Skip to main content
Solved

Combined Date & Duration field into a datetime field

  • November 12, 2023
  • 17 replies
  • 148 views
D
Forum|alt.badge.img+6

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

Best answer by Arthur_Tutt

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:

 

 

 

17 replies

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • Answer
  • November 12, 2023

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:

 

 

 

ScottWorld
J
D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

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

Arthur_Tutt
D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

@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')
D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

@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
Forum|alt.badge.img+25
  • Databaser
  • Brainy
  • November 12, 2023

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. 

Jan - Databaser
D
ScottWorld
Arthur_Tutt
Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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

 

 

 

 

 

 

 

  

D
Forum|alt.badge.img+2

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

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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

D
Forum|alt.badge.img+2

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 

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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. 

D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

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
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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

 

 

 

 

D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

@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

Arthur_Tutt
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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?

D
D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023

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
Forum|alt.badge.img+19
  • Arthur_Tutt
  • Brainy
  • November 12, 2023

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

 

D
Forum|alt.badge.img+6
  • David_Allen-Hu1
  • Author
  • Known Participant
  • November 12, 2023
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')
 
Terms & ConditionsAccessibility statement