Exclude date range from count formula

Platform discussions
, ,
1

Context: I’m reporting on the average duration of requests by type. In three different date columns, we capture the date a request was Added, the date work was Started, and the date work Ended. I have two additional Formula columns that total (1) the workdays between the Added and Ended dates and (2) the workdays between the Started and Ended dates. That part all works well! Formulas look like this:

  1. WORKDAYS({Ended}, {Added})
  2. WORKDAYS({Ended}, {Started})

Issue: The team handling these requests will be offline for two weeks (10 workdays), and I want to omit those days away from the reported duration of projects that were Added/Started before and Ended after their time away (but not from those Ended before or Added/Started after the break). There is no use case in which we’d exclude less than the full range; either 10 workdays are excluded from the count, or 0 are.

I’ve got the following formula in place for the Started–>Ended duration, but it seems to still be counting the 10 workdays I want to omit.

  • WORKDAYS({Ended}, {Started}) - IF(AND({Started} < DATE(2025, 12, 22), {Ended} > DATE(2026, 1, 6)), WORKDAYS(DATE(2026, 1, 7), DATE(2025, 12, 21)), 0)

With a test Started date of 12/19/25 and a test Ended date of 1/6/26, that formula is resulting in 13, when I want it to count only 3 (12/19, 1/5, and 1/6).

Question: Any advice on how to conditionally exclude a date range from a count?

2

Hi @robin.hogan,

I see 2 potential issues with your formula.

First, DATE(2025, 12, 22) returns “Mon Dec 22 2025 00:00:00 GMT-xxxx” and cannot properly be compared to a date column. You must wrap the DATE() in FORMAT_DATE(), e.g., FORMAT_DATE(DATE(2025, 12, 22),“YYYY-MM-DD”).

Second, I think your hard coded end date of the time off may be incorrect. It looks like it should be DATE(2026, 1, 3), depending on where exactly you want to end the week.

Try this:

WORKDAYS({Ended}, {Started}) - 
IF(
  AND(
    {Started} < FORMAT_DATE(DATE(2025, 12, 21), "YYYY-MM-DD"), 
    {Ended} > FORMAT_DATE(DATE(2026, 1, 3), "YYYY-MM-DD")
  ), 
  WORKDAYS(DATE(2026, 1, 3), DATE(2025, 12, 21)), 
  0
)

Jim - The Monday Man

1 Like
3

Hello @robin.hogan Yep. The fix is to subtract only the offline days that overlap your Started to Ended range.

Offline window
DATE(2025, 12, 22) to DATE(2026, 1, 6)

Formula

IF(
OR({Started} = “”, {Ended} = “”),
“”,
WORKDAYS({Ended}, {Started})

  • IF(
    AND({Started} <= DATE(2026, 1, 6), {Ended} >= DATE(2025, 12, 22)),
    WORKDAYS(
    MAX({Started}, DATE(2025, 12, 22)),
    MIN({Ended}, DATE(2026, 1, 6))
    ),
    0
    )
    )

This counts total workdays, then removes only the workdays that fall inside the break period for items that span across it.

Dr. Tanvi Sachar
Monday Certified Partner, Monday Wizard

4

The DATE vs formatted date thing bites a lot of people, so that first explanation is actually useful. That said, the overlap approach is cleaner long-term if the break window ever changes.

5

Thanks, Jim—that did the trick!

1 Like