[Solved] Work schedule

[RusselB]

Planning ahead for 2019; I'm trying to make a schedule so that days I won't be working are automatically filled in.
I've barely started on the project and have already run into a problem I've been unable to understand, and, therefore, unable to find a solution.
With the changes at my work, I will have Sundays, Mondays and Statutory holidays off. I created a small chart on Sheet2 for the Stat holidays.
My dates are in Column A and in Column B (for now), I want to return a 0 if the date in column A is a day I don't work, or a 1 if I do.
Everything seems to work fine for the first date of Jan 1 2019; but all of the rest of the dates (up to Feb 6, so far) return #NA

My spreadsheet is attached.

I'm looking for 1) an explanation and a solution... though an explanation alone might enable me to figure out a solution myself.

[njhub]

Hello RusselB
Try with:

Code: Select all

=IF(SUM(ISERROR(MATCH($A2,Sheet2.$A$1:$A$12,0)),NOT(OR(WEEKDAY($A2,2)=7,WEEKDAY($A2,2)=1)))>1)

because weekday cant be 0, 1 for sunday, 2 for monday,... 7 for saturday

[Villeroy]

If the date in A is a Sunday or a Monday or is in the table on Sheet2, then return 0 in column B; otherwise return 1

Code: Select all

=(WEEKDAY(A2;1)>2)*ISNA(MATCH(A2;$Sheet2.$A$1:$A$12;0))

weekday( d ; 1) returns 1 for Sunday, 2 for Monday. MATCH returns #NA in case of no match.
We search for weekdays > 2 and non-matching dates.

[RusselB]

Thanks to both of you.
@njhub: The WEEKDAY function can return 0, if the 2nd parameter is 3, but other than that, you are correct that the function will not return a 0

While I'm going to mark this as solved, since a couple of solutions were provided, I'm still at a loss as to why my original formula only worked for one entry.
I'd love to know why it worked for the date of Jan 1 2019, but returned #NA for any other date.

[njhub]