Assuming you input into cell A1 for month and year is a numerical entry such as 01/18, 1/18, 01/2018, or 1/2018, you can use the following formulas to develop your list.
Start by placing the following formula in B3.
=DATE(RIGHT(A1,LEN(A1)-FIND("/",A1)),LEFT(A1,FIND("/",A1)-1),1)+(WEEKDAY(DATE(RIGHT(A1,LEN(A1)-FIND("/",A1)),LEFT(A1,FIND("/",A1)-1),1))=1)
It looks a little complicated but that is due to trying to deal with the 4 possible different date entries in A1. If you have a different date entry format, a different formula will be required to develop the date for the first of the month.
In the formula above, it basically finds the "/" and uses its position to determine how much of the string to rip apart to grab the digits for the month and year respectively. After grabbing the digits for month and year, the information is dropped into the DATE formula which is looking for information in the follow format:
=DATE(year, month, day)
Since it is known that the date is the start of the month that is being investigated, set day = 1. The next part of the equation WEEKDAY determines the day of the week. IF the day of the week is SUNDAY the first day of the month needs to be increased by 1. Since a boolean result of TRUE is the equivalent of 1 in excel math operations and FALSE is the EQUIVALENT of 0, a simple addition of the WEEKDAY()=1 is made to the formula for determining the first of the month.
Once you have the seed for the start of your list, you need to add 1 to the date for the next row, and add an additional 1 to the date is the row above is a Saturday. Additionally you want to make sure your new date value does not exceed the end of the month, and for my procedure I also want to make sure the row above is not blank. Use the following formula in B4 and copy down far enough to ensure you cover the maximum number of possible dates.
=IF(B3<>"",IF(B3+1+(WEEKDAY(B3)=7)>EOMONTH($B$3,0),"",B3+1+(WEEKDAY(B3)=7)),"")
That will generate the list of dates skipping Sundays.
You have a couple of options for showing the day of the week. Option 1 is to do it with a formula. Basically the following formula will take the date from column B and and format the value to display the day of the week only as a string. In A3 use the following formula and copy down:
=TEXT(B3,"DDDD")
OR
=IF(B3<>"",TEXT(B3,"DDDD"),"")
The second equations will display blank if you wind up copying the formla down past the formulas in B and winds up referencing an empty cell instead oa a cell containing "".
![POC](../../I/static/images/d3c9d1d7beb3cd03c67ca5b71ffe3c34bfeca2fa61a2f50c3b6ec8f6b5a1e210.png)
how will the format of A1 look like? Oct 2018, 10/18, some other variation? Or perhaps the date for the first of the month? 18/10/01? if number format is being used, and the month is prior to October will there be a preceding 0? 01/18 or 1/18? – Forward Ed – 2018-11-08T20:01:52.690