Calculate workdays between two dates

Ann_Yeom · ‎Oct 10, 2023

Hi, hoping this is easy...

Can you help me adjust the formula below to return just the number of work days?

formula:

IF(
    AND({Squad Start Date}, {Squad End Date}),
    IF(
        AND(
            {Squad Start Date} < DATETIME_PARSE("10/28/2023"),
            {Squad End Date} >= DATETIME_PARSE("10/01/2023")
        ),
        DATETIME_DIFF(
            DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({Squad End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/28/2023"), "X"))), "X"),
            DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Squad Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2023"), "X"))), "X"),
            "days"
        ) + (MONTH({Squad End Date}) = 10),
        0
    )
)

Thank you so much!

Ann

avalon1990 · ‎Oct 11, 2023

check this, maybe it will help

Airtable does not provide a built-in WORKDAY_DIFF function to calculate workdays between dates. However, you can create your own custom function using Airtable formulas. Here's an example of how you can do it:

Start by creating a new field in Airtable to calculate workdays. Let's name this field "Workdays" (or whatever you prefer).

Then, use the following formula in this field:

IF(
AND({Squad Start Date}, {Squad End Date}),
IF(
AND(
{Squad Start Date} < DATETIME_PARSE("2023-10-28"),
{Squad End Date} >= DATETIME_PARSE("2023-10-01")
),
DATETIME_DIFF(
IF({Squad Start Date} > DATETIME_PARSE("2023-10-01"), {Squad Start Date}, DATETIME_PARSE("2023-10-01")),
IF({Squad End Date} < DATETIME_PARSE("2023-10-28"), {Squad End Date}, DATETIME_PARSE("2023-10-28")),
'days'
),
0
)
)

In this formula, we first check for the presence of Squad Start Date and Squad End Date. Then, we check if the dates fall within the specified range (from October 1, 2023, to October 28, 2023). If the conditions are met, the formula calculates the difference between the dates and returns the number of days.

Please note that the dates are in the "year-month-day" format (e.g., "2023-10-01"), and the DATETIME_PARSE function is used to convert strings to dates. You can adapt this formula to your specific dates and range.