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