Formula to count days between two dates that overlap the current week.

[andy_m]

I'm trying to write a formula that will count days between two dates (assignment_start_date and assignment_end_date) that overlap the current week, not counting weekends.

First example:

• current date is 4/5/2024
• assignment_start_date =  3/25/2024
• assignment_end_date = 4/2/2024

The result of this would be 2. As there are two days (4/1 and 4/2) that occur during the current week (3/31 - 4/6) and are in the range of 3/25 - 4/2

Second example:

• current date is 4/5/2024
• assignment_start_date =  3/25/2024
• assignment_end_date = 4/15/2024

The result of this would be 5. As there are 5 days (4/1, 4/2, 4/3, 4/4, 4/5) that occur during the current week and are in the range of 3/25 - 4/15

I've tried several versions of this with ChatGPT, however, I have yet to have any luck. The closet I've gotten is the following. However, sometimes the days are correct, but sometimes they are anywhere from 1 to 3 days off.

IF(
    AND(
        assignment_end_date >= DATETIME_FORMAT(DATEADD(TODAY(), -WEEKDAY(TODAY(), 'Monday') + 1, 'days'), 'YYYY-MM-DD'),
        assignment_start_date <= DATETIME_FORMAT(DATEADD(TODAY(), 7 - WEEKDAY(TODAY(), 'Monday'), 'days'), 'YYYY-MM-DD')
    ),
    MAX(
        0,
        MIN(
            DATETIME_DIFF(DATEADD(TODAY(), 7 - WEEKDAY(TODAY(), 'Monday'), 'days'), assignment_start_date, 'days'),
            DATETIME_DIFF(assignment_end_date, DATEADD(TODAY(), -WEEKDAY(TODAY(), 'Monday') + 1, 'days'), 'days'),
            DATETIME_DIFF(assignment_end_date, assignment_start_date, 'days')
        ) + 1
    ),
    0
)

[ScottWorld]

I don't think that you can do this with a formula, because it requires looping through dates, and formulas don't support looping. But you can do this with an automation where you write your own custom Javascript code. I don't know Javascript, but there are other Javascript experts here who might be able to chime in and help.

[andy_m]

@ScottWorld thanks for the quick reply. I had the same thought about Javascript after I posted this last night. I'm much better with JS, anyways.
I was trying to keep this lightweight, though, as there are thousands of records that will have to be processed daily to keep this column up to date. I'll probably have to do this through the API then, since the 30sec timeout on automations is pretty easy to hit. We shall just have to try and see.

[andy_m]

fyi. I indeed ended up doing this with JS and a script I'm running daily.