# Saturday date formula (=[month]1Sat)

#### kilroy82

##### New Member
I recently received a formula-driven annual calendar from a customer and was having some fun delving into the formulas to see how they work (especially because it has little arrows and you can scroll through the years, with the dates adjusting automatically).

I decided to build one for my own company, but first needed to change the fiscal start date from Feb 1 to Jan 1, which required moving Jan back to the beginning of the calendar (instead of the end). I was finally able to figure it out (so that's not what this question is about), but it required that I first learn about a new formula I had never seen before, and I was hoping someone on this forum could help explain it a little more (especially because it seems a little counterintuitive in the way that it behaves).

It's quite simple on its face--you just enter "=[month]1Sat" (e.g. =Jan1Sat, =Feb1Sat, =Mar1Sat, etc.) and it returns the numerical representation of the Saturday BEFORE the first day of that month. I tried googling this function to learn about it, but couldn't find ANYTHING on the internet about it (which seems very unusual). See below for a little table I built to show what I mean:

Since there's no way to enter a year (that I know of), it seems to be based on the current year (i.e. system date/time).

However (and here's the counterintuitive part), for some reason the Jan1Sat formula returns the Saturday before January 1 of NEXT year (i.e. the last Saturday of the current year). Does anyone know why this is? In order to account for this quirk, I had to built a little table and subtract 300+ days from the year to get it to return the correct date of Jan 2020.

Open to any and all comments to help me better understand this new formula!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Tetra201

##### MrExcel MVP
Does your formula-driven annual calendar have those names (Jan1Sat, etc.) defined in the Name Manager?

#### kilroy82

##### New Member
Ha! You hit the nail on the head, Tetra201! Never even thought to check the name manager (mostly because I didn't even know it existed before you said that). Excited to go in and learn all about what it can do for me!

Pack it up, boys... this case is closed! haha

Replies
0
Views
43
Replies
1
Views
24
Replies
7
Views
112
Replies
7
Views
65
Replies
8
Views
150