Calculate workdays between two dates

[Ann_Yeom]

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]

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.