# Combined Date & Duration field into a datetime field

Topic Labels: Dates & Timezones Formulas
Solved
1136 17
cancel
Showing results for
Did you mean:
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.

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
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:

17 Replies 17
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:

6 - Interface Innovator

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

6 - Interface Innovator

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

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?

12 - Earth

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

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.

4 - Data Explorer

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?

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?

4 - Data Explorer

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

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.

6 - Interface Innovator

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?

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

6 - Interface Innovator

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

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?

6 - Interface Innovator
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") )
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") )``

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: