Formula date add using "business hours"

[Will_Carter]

Hello community, I have a question about trying to create a schedule. We have created jobs that has an x amount of hours to do those jobs. So I have a start date for example 7/1/2021 and the job is to take 20 hours which would be two 10 hour days. When I try DATEADD({Start},{Duration},‘hours’) or replace ‘hours’ for ‘days’ it literally adds 2 days for a completion date of 7/3/2021 but that’s wrong. It should be 7/2/2021.

I tried to format the start date to include a time, however, Airtable looks at everyday as a 24 hour day. So then I come up with start time of 7/1/2021 7:00am and completion date/time of 7/2/2021 3:00am.

Is it even possible to create business hours so when calculating start and completed by the dates are correct? Because ultimately I’m trying to set my start and duration as a Gantt view to show my forecasted schedule.

[Justin_Barrett]

Welcome to the community, @Will_Carter! :grinning_face_with_big_eyes: Working with business hours is definitely doable. It’s mostly a matter of dividing the total number of hours needed for a task by the length of your workday, rounding up so that a partial day still adds a whole day, then subtracting one (if a job will take one day, you don’t really want to add one day because it can be finished on the same day that it starts). Here’s the formula that I came up with:

IF(Duration, DATEADD({Start Date}, ROUNDUP(Duration / 10, 0) - 1, "days"))

For this to work, I activated the “Use the same time zone (GMT) for all collaborators” option in the formula field formatting. If you prefer to leave this setting off, then remove the - 1 from the formula.