Formula Help (Calendar) Expanded

[Cybrex]

I posted my original question here because it aligned with what was already being discussed (I also was using [B]jtakw[/B] coding as well so it made sense to post there but it was a really old post. So I was told to start my own!)


Hey All, I'm resurrecting this post because I have a question that works off of this (as I'm using this same formula for mine, but for some reason it wouldn't copy correctly across the row so I had to manually update where to target the year for every cell, lol).

To follow up on this, the calendar we have is used for work. Specifically to track vacation days, what days people are in the office and which office and holidays. I've attached a snippet of what it looks like to help make it easier to understand what I'm looking for.

1) Is there a way to align the day of the month with the correct date. Ie. This screenshot is supposed to be Dec 2024 but for some reason the first day it's showing is a Monday, but when I look at the Calendar it shows the 1st should be a Sunday. So not sure what's happening here?

2) Is there a way to move the column coding we have for the week days/ weekends so they align with the Days of the week at the top instead of me having to copy/paste them to align.

3) The big blue bars shown on here are marked for holidays. Is there a way I can assign another sheet to Canadian holidays and they'll update those blue bars to align with the correct days of those months?

 

[Cybrex]

Based on the minisheet, I found one issue. You're probably familiar with the original philosophy behind formulas. You put a formula in a cell, and it displays a value in that cell. A few years ago Microsoft came up with "Spill" formulas. These formulas can create multiple values, and these values "spill" into adjacent cells. If the adjacent cells already have something in them, you get a SPILL error. And that's what's happening here. I created a Spill formula for the C5 cell, and D5:AG5 already have something in them. So delete everything in D5:AG5, and put this formula

=LET(d,DATEVALUE(B4&" 1, "&AH4),SEQUENCE(,DAY(EOMONTH(d,0)),d))

in C5 ONLY! D5:AG5 will autofill. Do the same with the row below. Clear D6:AG6 and put

=C5#

in C6 ONLY!

Then for the formatting, select C5:AG5, right click on that range, select Format Cells, use the Number tab, click Custom, and in the Type: box put ddd and click OK. Then select C6:AG6, right click on that range, select Format Cells, use the Number tab, click Custom, and in the Type: box put d and click OK.

Let me know how that works. Then we can look at the Conditional Formatting for the weekends and holidays, which both rely on the C5 formula working right.

We've got it working now! Found some other stuff going on in the background that was interfering with the numbering format and had to figure out what it was and the get rid of it. But now January days updated based on the year!

Added back the holidays on sheet 15.

 

[Eric W]

Great! Let me know if you need anything else.

 

[Cybrex]

Great! Let me know if you need anything else.

Sorry, my bad. What I meant is, I managed to get your formula's working on all sheets in regards to the date's, days, years, and then after that I added in a new sheet for the holidays, and then stopped there. Realized, I wasn't as smart as I think I am as I've never done formula's for consolidate.

I'm definitely going back and relearning excel, lol

 

[Cybrex]

Great! Let me know if you need anything else.

So, forgetting you had written the holiday/weekends, I finally went back this morning after failing to figure it out myself. Once I updated the weekend formula it worked like a charm. Unfortunately, the holiday one doesn't want to work. When I try to setup the conditional formatting, it continues to say I cannot reference another sheet in the formula.
I've also tried to highlight the cells themselves and place the formula in the bar, but that just causes another mess.

=MATCH(C$5,Sheet15!$B$4:$B$14,0)

This is how I've updated it for my calendar. C5 is the starting cell for my weekdays/weekends, sheet 15 is my holidays sheet. I also deleted Sheet15!$B$4:$B$14 from the formula ad highlighted the cells myself but it fails as well. So close, yet so far! lol

 

[Cybrex]

Never mind! I finally have it working and its beautiful! Thank you so much @Eric W for all your help!

 

[Eric W]

I'm glad you got it working and learned something along the way!