creating a list of weekend and holiday dates

Hi all,
Long time lurker, first time poster here

I'm having trouble creating a formula that generates a list of weekend dates and holidays. And for this purpose I'm considering Friday to be a weekend day.

I started with workday.intl(A3,1,"1111000") where A3 is date(B1,1,1). The year is specified in B1 so I can copy forward this sheet for years to come. The holidays I generate with formulas so that is dynamic and will adjust from year to year. If the holiday falls on a Sat/Sun, it is observed on the previous/following weekday. That is to say if Christmas is on Sunday then Monday is considered a day off.

So I created a named range of these observed dates to include into my calendar/list.

=if(countif(ObservedDates,$A12+1)>0,$A12+1,workday.intl($A12,1,"1111000"))

Unfortunately this formulas misses all holidays that fall on Tue, Wed, or Thu.

Anyone have thoughts or ideas? Thanks for reading!
-Michelle

Thanks so much for your quick reply! I've never used the Let function so that was very cool to learn.

I also new to the filterxml function - am I understanding correctly that it is to split the calculations into separate cells rather than have all the results populate one cell?

I'm excited to try to apply this lesson further because I want to include additional days as holidays. For example, if Christmas falls on a Tue, Wed, Thu, or Fri I'd like to include Christmas day - 1 (aka Christmas eve) as a holiday. So hear me out, I think I should be able to generate that list using the Let function just like you showed me. Don't give me the answer, I'm going to try and crunch this out myself

Thanks again for all your knowledge!

At what stage do you have difficulty? Maybe I can find working solution here to the problem.

Sorry for delayed reply but I've been out sick with a cold. Today, I realized how amazing filterxml is. I didn't really understand it the first (or fourth) time I looked at it and it took several more pass-throughs for me to get it. But now I finally see it. What a neat little function!

Okay. So for the next part of my problem. I want my list of Observed days to include the day before if the holiday falls on Tue, Wed, Thu, or Fri. For instance, if Christmas falls on Friday, then Christmas Eve, Thursday, would be considered a weekend day/non-working day/holiday.

Now, when one's only tool is a hammer, everything starts to look like a nail. So I tried doing this with my newfound LET tool. Except I don't know how to generate a result of two different dates given one input. You can see what I attempted, which was create a string of incomprehensible numbers. Bummer. Filterxml will separate those values for me but I run into a spill problem because of the values in the cells above and below. .

Thoughts? You don't have to give me the answer straight away but can you point me in the right direction?