Combine Options for Single Argument

I am trying to use the WORKDAY() function. The third parameter can be holidays to skip. I have generated 7 formulas to determine the 7 holidays of a given year. Thus my formula I would like to look something like the following: =WORKDAY(A1,1,{NewYears,MemorialDay,IndependenceDay,...}) where formulas would take the place of each holiday looking something like DATE(YEAR(A1),7,4) for IndependenceDay or DATE(G1,11,1)+21+CHOOSE(WEEKDAY(DATE(G1,11,1)),4,3,2,1,0,6,5) for Thanksgiving. etc...

The temporary solution is to create a range elsewhere with those holidays each taking up a cell in some contiguous block where I could instead make the formula look like =WORKDAY(A1,1,Holidays!$A$1:$G$7). Unfortunately, if I'm still using this worksheet 8 years later, the function becomes useless.

The second channel I pursued was to make the other sheet called "Holiday" have only 7 cells, each determined by the NOW() function [i.e. =DATE(YEAR(NOW()),12,25) for Christmas] Unfortunately, once a new year hits, all the data for the previous year will get shifted in the column referencing the holidays but not the other columns, rendering it again, useless.

Any suggestions are appreciated. Thank you.

[Edit: I figured out how to make it work. MAX(WORKDAY(A1,1,{HolidayFormula1}),WORKDAY(A1,1,{HolidayFormula2}),WORKDAY(A1,1,{HolidayFormula3}),...) If any holidays happen to match up, it will return a later date than all the other WORKDAY() Functions. Add in a special case for the day after Thanksgiving and it will work. I'm still in the market for a more "elegant" solution if anyone knows of it, but You can technically consider the problem solved.]

Hi Dimonium_Anonimo. Welcome to the forum.

One way would be to modify your YEAR(NOW()) formula. Make a contiguous row of years. Use that as both a reference in place of YEAR(NOW()) and as a lookup range. Then make a 7 cell range below each of those with holidays unique to that year. Put that lookup table in the sheet 'Holidays'.

A short example:

	A	B	C	D	E	F	G	H	I	J
1	2014	2015	2016	2017	2018	2019	2020	2021	2022
2	12/25/2014	12/25/2015	12/25/2016	12/25/2017	12/25/2018	12/25/2019	12/25/2020	12/25/2021	12/25/2022	In A2 : =DATE(A$1,12,25)
3	7/4/2014	7/4/2015	7/4/2016	7/4/2017	7/4/2018	7/4/2019	7/4/2020	7/4/2021	7/4/2022	In A3 : =DATE(A$1,7,4)
4	11/27/2014	11/26/2015	11/24/2016	11/23/2017	11/22/2018	11/28/2019	11/26/2020	11/25/2021	11/24/2022	In A4 : =DATE(A$1,11,1)+21+CHOOSE(WEEKDAY(DATE(A$1,11,1)),4,3,2,1,0,6,5)

Formula:

Please Login or Register  to view this content.