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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Does your formula-driven annual calendar have those names (Jan1Sat, etc.) defined in the Name Manager?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top