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]

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. 

[David_Allen-Hu1]

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]

@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-Hu1]

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]

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?

[David_Allen-Hu1]

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]

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

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