Notifications
Clear all
General Excel Questions & Answers
2
Posts
2
Users
0
Reactions
125
Views
Topic starter
I would like to create a formula that lists the date for the 1st Monday of each month.
Posted : 27/10/2019 4:02 pm
Let's say you want a list of 12 months worth of the first Monday of each month starting with January 2019. First select 12 cells in a column, then enter the following array formula:
=DATE(2019,{1;2;3;4;5;6;7;8;9;10;11;12},1)+6-WEEKDAY(DATE(2019,{1;2;3;4;5;6;7;8;9;10;11;12},1)-1,3)
Press CTRL+SHIFT+ENTER to complete the formula.
Or this one, which uses ROW to generate the array of 1 to 12:
=DATE(2019,ROW(1:12),1)+6-WEEKDAY(DATE(2019,ROW(1:12),1)-1,3)
Also requires CTRL+SHIFT+ENTER to complete the formula.
If you have Office 365 and the new dynamic array formulas you can use this formula with the SEQUENCE function:
=DATE(2019,SEQUENCE(12),1)+6-WEEKDAY(DATE(2019,SEQUENCE(12),1)-1,3)
Mynda
Posted : 29/10/2019 7:37 am
