Dumb Excel Questions

:woman_shrugging:

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.

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.)

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.

1 Like

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.

1 Like

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.

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.

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”))

Have you tried the WORKDAY function?

1 Like

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? 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).

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. :roll_eyes:

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!”

:exploding_head::exploding_head::exploding_head:

: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

Um, how about if column A is successful, B = A, etc.

2 Likes

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.)

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.

That’s messy-lookin’ , imo

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. :crossed_fingers:

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.

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

I would do the following:

B: if(A<>“.”,A,iferror(b calc, “.”))
C: if(B<>“.”,B,iferror(c calc, “.”))