Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 31, 2024 06:38 PM
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:
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:
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
)
Mar 31, 2024 08:39 PM - edited Mar 31, 2024 08:39 PM
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.
Apr 01, 2024 05:35 AM
@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.
Apr 02, 2024 01:21 PM
fyi. I indeed ended up doing this with JS and a script I'm running daily.