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.]
Bookmarks