Saturday date formula (=[month]1Sat)

kilroy82

New Member
Joined
Oct 6, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

1602009242460.png


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!
 

Some videos you may like

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
Joined
Oct 14, 2016
Messages
3,596
Does your formula-driven annual calendar have those names (Jan1Sat, etc.) defined in the Name Manager?
 

kilroy82

New Member
Joined
Oct 6, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,959
Members
410,586
Latest member
acadavid86
Top