See below for a formula that will automatically return the first day of the current month.
The solution explained below is based on theSEQUENCE function.
SEQUENCE is one of the originaldynamic array functions in Excel, and a perfect fit for this problem.
Background study
Short version
The explanation below is rather long.
This works becauseExcel dates are just serial numbers.
This is handled with theCHOOSEandWEEKDAYfunctions.
Conditional formatting is used to highlight the current date and holidays and to lighten days in other months.
Read below for all the details.
Basic SEQUENCE
The SEQUENCE function can be used to generate numeric sequences.
The arrayspillsinto a vertical range of ten cells.
SEQUENCE can generate arrays in rows and columns.
We don’t want to display serial numbers in our calendar, we want to show days.
To do that we can use the custom number format “d”.
That will cause Excel to display just the day numbers.
To make a proper calendar, we need the first day in our grid to start on Sunday.
How can we calculate the last Sunday of the previous month?
Before we get into specific functions, let’s clarify the goal.
First Sunday
If the first of a month happens to be a Sunday, we’re done.
There’s no need to do anything.
The first of the monthisour start date.
How many days do we need to roll back?
This depends on what day of the week the first day of a month lands on.
For example, if the first is a Tuesday, we need to roll back 2 days.
If the first is a Friday, we need to roll back 5 days.
And if the first is already a Sunday, we need to roll back 0 days.
The WEEKDAY function
To figure out the day of the week, we use theWEEKDAY function.
WEEKDAY returns a number for each day of the week.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday.
The CHOOSE function
TheCHOOSE functionis used to select arbitrary values by numeric position.
The other individual values given to CHOOSE are the rollback numbers, one for each day of the week.
If the start date in J6 is changed to another first-of-month date, the grid automatically updates.
Holidays must be a range that contains valid Excel dates that represent non-working days.
In the example shown,holidaysis thenamed rangeL6:L8.
Alternatively, you’re free to define holidays as anExcel Tableso the range updates automatically.
This is a better option than merging cells since it doesn’t alter the grid structure in the worksheet.
The local variable “start” overrides the named rangestarton the worksheet, which can be deleted if desired.
By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable.
you’re free to use the WEEKDAY function inside other formulas to check the day of week.
The values provided to CHOOSE can include references.
The TODAY function takes no arguments.
you’re free to format the value returned by TODAY with a datenumber format.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
Related videos
The SEQUENCE function
SEQUENCE of dates
