How to configure a dateadd / weekday formula to return a time only within specified business hours

[Aysia_Saylor]

Hello,

Is there a way to configure this formula to only return a time during specified business hours (7:30-4PM)?

DATEADD(WORKDAY({Last Modified},1))

Thanks :slightly_smiling_face:

[Dimitris_Goudis]

Hi @Aysia_Saylor,

could you describe more the issue you want t o solve?

In a first view I get that you want the date of specific date fact. Is that right ?

Thanks

[Justin_Barrett · Accepted solution]

@Dimitris_Goudis As I read it, @Aysia_Saylor wants to take the date from the {Last Modified} field, add one workday to it, and then ensure that the time portion of the result falls within specific hours of a day.

@Aysia_Saylor It’s actually a little easier to target whole hours and ignore minutes, so I set this up to keep the new time within the range of 8 am to 4 pm:

DATEADD(
    WORKDAY({Last Modified}, 1),
    MAX(MIN(HOUR({Last Modified}), 15), 8) - HOUR({Last Modified}),
    "hours"
)

The HOUR() function returns the hour of a given datetime in 24-hour format (specifically 0-23, with 0 being midnight). The nested MAX() and MIN() functions take that hour and force it to be between 8 (8 am) and 15 (3 pm¹). We then take that result and subtract the original hour, which gives us an offset value to use in DATEADD(). For example, if the original hour is 1 (1 am), it’s forced to 8; 8 - 1 = 7, meaning we need to add 7 hours. On the other end, if the original hour is 22 (10 pm), it’s capped to 15 (3 pm). 15 - 22 = -7, meaning we need to subtract 7 hours. All hours between 8 and 15 return an offset of 0, so they’re left unmodified.

Now, this will only work as written if you live in GMT because dates are stored internally relative to GMT. Even if the {Last Modified} field is set to display local time, the HOUR() function will give you the GMT hour because it’s operating on the raw data, not the displayed version. To force it to reflect your local timezone, you’ll need to tell Airtable to recalculate the time using SET_TIMEZONE() before the HOUR() function gets it:

DATEADD(
    WORKDAY({Last Modified}, 1),
    MAX(MIN(HOUR(SET_TIMEZONE({Last Modified}, "America/Los_Angeles")), 15), 8)
    - HOUR(SET_TIMEZONE({Last Modified}, "America/Los_Angeles")),
    "hours"
)

I used “America/Los_Angeles” for the timezone because that’s the zone in which I live. Change that to the appropriate timezone indicator for your region.

---

¹ The hour caps a 3 pm because we’re only checking the hour, not the minutes. If the minutes are above 0, capping at 4 pm would mean a time like 4:03 might get through. Capping at 3 pm means that all times will be before 4 pm, not after.

[Dimitris_Goudis]

Hi @Justin_Barrett,
thanks for the solution and the clear explanation. At the beginning I didn’t get the issue like this.

Thanks

[Aysia_Saylor]

Thank you Justin! This works great. I appreciate you explaining the reasoning behind it too so I can replicate that logic in the future when creating other formulas.

[Joel_Foster]

Hello! I am using a formula that adds time to a due date field and trigger date field based on the frequency of for which task occur. As of now I use the DATEADD function to add time to these date fields with the following formula: 

IF(Frequency='Monthly', DATEADD({Due Date}, 1, 'month'), IF(Frequency='Quarterly', DATEADD({Due Date}, 3, 'month'), IF(Frequency='Annually', DATEADD({Due Date}, 1, 'year'), BLANK())))

But this formula does not take into account weekends. I am trying to work in the WORKDAY function but am doing so unsuccessfully. Here is what I have come up with so far.

IF(Frequency = "Monthly", WORKDAY({Due Date}, 30,1,0), IF(Frequency = "Quarterly", WORKDAY({Due Date}, 90,1,0), IF(Frequency = "Annually", WORKDAY({Due Date}, 365,1,0), BLANK())))

The function above returns an error. Any insight would be much appreciated.