# Dumb Excel Questions **URL:** \ **Category:** Software/Technology\ **Created:** [February 3, 2022, 6:03pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148 "2022-02-03T18:03:06Z")\ **Posts on this page:** 20\ **Page:** 7
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 14, 2023, 7:53pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/122 "2023-01-14T19:53:59Z")
> [@BruteForce](#): > > I don’t understand. If I put dates in one column (starting in A2) and then in B2 I put TEXT(WEEKDAY(A2,1),“dddd”), the filter at the top just shows the day names. What are you doing that’s different? 🤷‍♀️ When I’m back at work I’ll post a screenshot. 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. ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 14, 2023, 8:00pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/123 "2023-01-14T20:00:46Z")
That’ll narrow it down to just holidays that I have to manually check for… so far I’ve just checked manually. I might end up brute-forcing it another way. Set a class of due dates (15th of the month, end of the month, etc) and then just look them all up. If something’s due on the 25th of the month, for December 2022 I need to return that the due date is actually Tuesday, December 27 since December 26 is Christmas-observed. There’s so many stupid one-offs like that I might just need to manually look them all up. (Yes, December 2022 is in the past, but you get the idea… that’s the kind of nuance I need to capture.) ---
**Author:** ![dr\_t\_non-fan](https://community.goactuary.com/letter_avatar_proxy/v4/letter/d/a8b319/32.png) [@dr\_t\_non-fan](https://community.goactuary.com/u/dr_t_non-fan)\ **Post date:** [January 15, 2023, 6:27am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/124 "2023-01-15T06:27:47Z")
You can make a list of your holidays, then refer to it in a formula. You’d have to update it as you learn of new holidays. ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 16, 2023, 5:29am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/125 "2023-01-16T05:29:06Z")
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.” ---
**Author:** ![DeepPurple](https://community.goactuary.com/user_avatar/community.goactuary.com/deeppurple/32/1551_2.png) [@DeepPurple](https://community.goactuary.com/u/DeepPurple)\ **Post date:** [January 17, 2023, 5:37pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/126 "2023-01-17T17:37:29Z")
> [@dr\_t\_non-fan](#): > > You can make a list of your holidays, then refer to it in a formula. You’d have to update it as you learn of new holidays. > [@twig93](#): > > 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. 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. ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 17, 2023, 7:31pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/127 "2023-01-17T19:31:47Z")
Yeah, that might be easier. 1. List all 365 days (easy) 2. Filter out weekends (easy) 3. Remove major holidays 4. Determine number of lists needed 5. Copy paste as many times as needed 6. Remove minor holidays from individual list as applicable I got tagged with a ton of other crap, so I haven’t had a chance to work on this in a few days. ---
**Author:** ![Kenny](https://community.goactuary.com/user_avatar/community.goactuary.com/kenny/32/53_2.png) [@Kenny](https://community.goactuary.com/u/Kenny)\ **Post date:** [January 17, 2023, 7:50pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/128 "2023-01-17T19:50:54Z")
This does not sound easier if it must be done annually. Creating a formula and updating the holiday list each years sounds easier to me. However, it might easier to “fix” if this responsibility is passed to someone who doesn’t do well in XL. ---
**Author:** ![Kenny](https://community.goactuary.com/user_avatar/community.goactuary.com/kenny/32/53_2.png) [@Kenny](https://community.goactuary.com/u/Kenny)\ **Post date:** [January 17, 2023, 7:58pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/129 "2023-01-17T19:58:18Z")
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). IFERROR(INDEX(Holidays!$B:$B,MATCH($A2,Holidays!$A:$A,0)),IF(WEEKDAY(A2,2)\>5,“Weekend”,“Week day”)) ---
**Author:** ![Kenny](https://community.goactuary.com/user_avatar/community.goactuary.com/kenny/32/53_2.png) [@Kenny](https://community.goactuary.com/u/Kenny)\ **Post date:** [January 17, 2023, 9:05pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/130 "2023-01-17T21:05:55Z")
Have you tried the WORKDAY function? > **[WORKDAY function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/workday-function)** > > Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number... ---
**Author:** ![AlexioXela](https://community.goactuary.com/user_avatar/community.goactuary.com/alexioxela/32/550_2.png) [@AlexioXela](https://community.goactuary.com/u/AlexioXela)\ **Post date:** [January 17, 2023, 10:33pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/131 "2023-01-17T22:33:26Z")
It seems like the workday function combined with a list of holidays (see [How to check if a date is public holiday and count days except holidays in Excel?](https://www.extendoffice.com/documents/excel/2602-excel-if-date-is-public-holiday.html) for formulas for 10 US holidays) would be the best way to do it. If you want the work day coincident with or following [day] it would be something like Workday([day]-1,1,[holidays]). Some of the holidays at that link would likely need their formula modified though, for example, if Christmas is observed on the next business day, then it would be workday(date([year],12,25-1),1). ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 17, 2023, 11:39pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/132 "2023-01-17T23:39:50Z")
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!” 🤯🤯🤯 ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [January 20, 2023, 9:14pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/133 "2023-01-20T21:14:04Z")
:bump: New issue… > **read all about it here...** > > 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. …but… F1=UNIQUE(A1:E1,TRUE,TRUE) is the answer ---
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [January 20, 2023, 9:36pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/134 "2023-01-20T21:36:24Z")
Um, how about if column A is successful, B = A, etc. ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [January 20, 2023, 9:41pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/135 "2023-01-20T21:41:15Z")
> [@twig93](#): > > So I’m already going to have multiple lists that will capture all of this nonsense. 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.) ---
**Author:** ![SredniVashtar](https://community.goactuary.com/user_avatar/community.goactuary.com/srednivashtar/32/2894_2.png) [@SredniVashtar](https://community.goactuary.com/u/SredniVashtar)\ **Post date:** [January 20, 2023, 9:48pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/137 "2023-01-20T21:48:34Z")
> [@twig93](#): > > I’m also working on seeing how much I can pawn off on the gal who started yesterday. Every office needs like an 18 year old wiz kid who can do all this shit for $15/hr. It’s a gross mistake that we pay us oldsters $millions to fail at xlookup. ---
**Author:** ![1695814](https://community.goactuary.com/user_avatar/community.goactuary.com/1695814/32/38506_2.png) [@1695814](https://community.goactuary.com/u/1695814)\ **Post date:** [January 20, 2023, 10:06pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/138 "2023-01-20T22:06:52Z")
That’s messy-lookin’ , imo ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 20, 2023, 11:19pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/139 "2023-01-20T23:19:01Z")
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. 🤞 ---
**Author:** ![twig93](https://community.goactuary.com/user_avatar/community.goactuary.com/twig93/32/527_2.png) [@twig93](https://community.goactuary.com/u/twig93)\ **Post date:** [January 20, 2023, 11:21pm UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/140 "2023-01-20T23:21:38Z")
> [@SredniVashtar](#): > > Every office needs like an 18 year old wiz kid who can do all this shit for $15/hr. This is like a 60 year old whiz who must be making a LOT more than $15 / hr. But if she can pick up the stuff that Mean Girl was doing that I don’t know how to do, I’m thrilled. ---
**Author:** ![SteveGrondin](https://community.goactuary.com/user_avatar/community.goactuary.com/stevegrondin/32/3257_2.png) [@SteveGrondin](https://community.goactuary.com/u/SteveGrondin)\ **Post date:** [January 21, 2023, 3:58am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/141 "2023-01-21T03:58:58Z")
A: iferror(a calc, “.”) B: if(A=“.”,iferror(b calc,“.”),A) C: if(B=“.”,iferror(c calc,“.”),B) … Seems good to me, only a small amount of nesting ---
**Author:** ![Vorian\_Atreides](https://community.goactuary.com/user_avatar/community.goactuary.com/vorian_atreides/32/65623_2.png) [@Vorian\_Atreides](https://community.goactuary.com/u/Vorian_Atreides)\ **Post date:** [January 22, 2023, 1:48am UTC](https://community.goactuary.com/t/dumb-excel-questions/5148/142 "2023-01-22T01:48:00Z")
> [@SteveGrondin](#): > > A: iferror(a calc, “.”) > B: if(A=“.”,iferror(b calc,“.”),A) > C: if(B=“.”,iferror(c calc,“.”),B) I would do the following: B: if(A\<\>“.”,A,iferror(b calc, “.”)) C: if(B\<\>“.”,B,iferror(c calc, “.”)) [Previous page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=6) [Next page](https://community.goactuary.com/t/dumb-excel-questions/5148.md?page=8)