ETA: Oh, wait, I’m not putting the TEXT part in the formula and specifying the format that way.
I just have =WEEKDAY(A2) and then I clicked on the column and “format cells” and picked dddd.
Your way might work… I’ll try Monday. It will bloat up the workbook though as it makes a repeated formula a lot longer than it needs to be. This formula will probably get repeated several thousand times. But… might still be the best option.
Yeah, I’d have to check for weekends first, then check for holidays, but you’re right, I just need to list out the holidays.
Annoyingly… it’s at least two different lists of holidays because the “half-holidays” are observed by some players but not others. Stuff like MLK Day… a holiday for this purpose but not for that one. So it’s more like “check for weekends, check which holiday list to consult, check for applicable holidays.”
It might be easier, and I mention it only to plant the seed of an idea, instead of referencing a list of days that are holidays or weekends, it might be more straightforward just to list workdays that are NOT holidays and weekends. Instead of carving out days that are holidays, just pre-check a list of all days that are not holidays or weekends.
I just remembered I literally have a formula that does something like this. I have a column that returns “Week Day”, “Weekend”, or the name of the Holiday based on the date, then used that for conditional formatting for a spreadsheet. XL has a built in WEEKDAY function so there isn’t a need to do complicated math. Current date is in column A, holiday dates are on tab Holidays in column A and name of the holiday in column B. This formula is in B2 (not that it really matters).
I’m definitely not relying on any lists of holidays that aren’t specific to my purposes. As I said, there’s so much nuance… MLK Day counts as a holiday for this but not for that. This organization observes all holidays the following business day (so July 4 on a Saturday is observed Monday) but that organization does the nearest day (so July 4 on a Saturday is observed Friday). And this third organization observes a holiday on November 3rd because that’s the founder’s birthday.
So I’m already going to have multiple lists that will capture all of this nonsense.
And the turnaround on this stuff is tight. I might not get the data until two days before something is due, so I really need to have my ducks in a row. And if I’m taking time off I need to know what to assign out to others based on actual due dates.
I’m also working on seeing how much I can pawn off on the gal who started yesterday. She seems really on top of this stuff and unlike me, she’s done it before so it’s more second nature to her. I actually heard her arguing with my client/boss “no this isn’t due until the day after tomorrow because Martin Luther King Day postpones all of this week’s due dates by one business day” … “oh yeah, that’s right, I forgot about that!”
In trying to avoid a bunch of nested ifs, I set up five columns of formulas.
Col A tries to calculate something (text). If it can’t, it passes a “.” (text).
Col B, if it sees that Col A = “.” then it tries to calculate something. If it can’t (or if col A was successful), it passes a “.”.
Col C, third verse same as the second, except it first check that both Col A & B are “.”
Et cetera for both col’s D & E.
Only one of these columns will have a non-“.” value.
At least one of the columns will have a non-“.” value.
Now, over in Col F I want to pass the non-“.” value.
Ideas on how to accomplish that? It’s like INDEX/MATCH but you’re matching on the value that is not the default-value.
I’d probably use a table, with relevant dates on the left, and a column for each client.
If you want to be super simple, make a row for every single date. Which theoretically will make it harder to eff up the data entry. You can also handle things like weekends in that table, rather than in your ugly formula.
(assuming here you aren’t planning for 2,740 years into the future, in which case of course you should do the same, but in MS Access.)
The list of holidays is task-specific rather than client specific, but I follow your gist.
So far I haven’t done anything because for the stuff that I know will be my responsibility I already manually listed out all of the dates through the end of the year anyway and so far it’s looking like the new gal is working out great so she might get most of Mean Girl’s work.