CODEWITHSUNDEEP
regexgoogle-sheetsfiltergoogle-sheets-formulaarray-formulas
TC76
TC76

Reputation: 870

FILTER non-working days from range/list of dates

I'm trying to create a list of dates between a start date and an end date (done). But now, I want to FILTER weekends out of that list.

The start date is defined, but the end date is based on a number of working days after the start date. The problem is, when I create the list using the following formula, all dates in between are included and I've made numerous attempts to FILTER said dates using WORKDAY.INTL and REGEXMATCH without success. Is it possible to modify this particular formula or do I need to start over with something different?

=ArrayFormula(TO_DATE(row(indirect("A"&A2):indirect("A"&B2))))

Here is an example of what I've done.

This is what I'm getting:

This is what I'm after:

Upvotes: 0

Views: 267

Answers (2)

player0
player0

Reputation: 1

you can do it like this:

=ARRAYFORMULA(FILTER(ROW(INDIRECT("A"&A2&":A"&B2)), 
     REGEXMATCH(TEXT(ROW(INDIRECT("A"&A2&":A"&B2)), "ddd"), "[^(Sat|Sun)]")))

0

Upvotes: 1

JPV
JPV

Reputation: 27262

See if this works

=query(ArrayFormula(TO_DATE(row(indirect("A"&A2):indirect("A"&B2)))), "where dayOfWeek(Col1) <> 7 and dayOfWeek(Col1) <> 1")

Upvotes: 1

Related Questions