List First Monday Date in Each Month Excel - KING OF EXCEL

Monday, May 25, 2020

List First Monday Date in Each Month Excel

List First Monday Date in Each Month Excel

With Excel formulas we can easily list the first Monday date in each month, or the last Sunday, or whatever day you like.
I’ll use the DATE and WEEKDAY functions to generate the list of dates, and then I’ll show you some alternatives, including a dynamic array solution using SEQUENCE. Plus, I’ll connect the formula to a data validation list that lets you choose which day to display.

Download Workbook


Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download. 

List First Monday Date in Each Month Formula

The formula is:
=DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},7)- WEEKDAY(DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)
And you can see the results in Office 365* spill to the cells below, generating a list of 12 dates:
list first monday date in each month
*Note: Excel 2019 and earlier users must select 12 empty cells first, then enter the formula with CTRL+SHIFT+ENTER. The spill functionality is currently only available in Office 365 for users on the Insider Channel.

I’ll dive into explaining the formula in a moment, but first the syntax for DATE and WEEKDAY are below:
=DATE(year, month, day)
=WEEKDAY(date, return_type)
Where return_type is selected from this list:
return_type
Click the links below to see comprehensive tutorials for these functions:
DATE function tutorial
WEEKDAY function tutorial

List First Monday Date in Each Month Formula Explained

We can break the formula down into 3 parts which I’ve colour coded below:
=DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},7)-

WEEKDAY(

DATE(2020,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1

,3)
Part 1: Generates a list of dates for the 7th of each month. The month argument contains an array of values 1 to 12, one for each month.
Part 2: Generates a list of dates for the 1st of each month minus 1 day. i.e. the last day of the previous month.
Part 3: Finds the WEEKDAY number of the last day of the previous month (from part 2).
Finally, part 3, the weekday number is subtracted from part 1 to return the first Monday date for each month.
The image below shows the formula broken down into steps:
list first monday date in each month formula explained
 

Choose First Day of Each Month to Display

With some data validation we can allow the user to choose which day they want listed by altering the number of days to subtract from the last day of the month:
first day in month
 

Choose Last Day of Each Month to Display

Alternatively, with the EOMONTH function we can modify the formula to list the last day of the month. It requires an adjustment to the number of days we need to subtract (see table in blue):
last day in month
 

List First Monday Date in Each Month Formula with ROW

Instead of listing the days of the months manually inside the DATE formula i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, we can use the ROW function to automatically generate them:
list first monday date in each month formula with ROW
 

List First Monday Date in Each Month Dynamic Array Formula

Alternatively, for those of us with Office 365 and dynamic arrays we can use the SEQUENCE function to generate the 12 month numbers:
list first monday date in each month dynamic array formula
No one formula is better than the other, so you’re free to choose which one you use.
Have a great day,
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment