I have tried a few "Date" formulas but I cannot get this to work.

Here is my issue:

I want to set up a spreadsheet that will allow the user to type in A1 - the month and year.

I would like my spreadsheet to automatically populate Column A with "Days" of the month e.g. if the month begins on Wed, then Wednesday will be the first day. I would then like column B to populate dates e.g. 01/12/2018 (UK format).

Also, I would like Sunday to be omitted. I have a manual version of this where I must type in the values each month but I would like to automate it as this is required to be done 80 times and I don't want to have to firstly populate manually, then copy and paste 79 times.

Any suggestions?

New contributor
Grungefreak is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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 Nov 8 at 20:01

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

Enter value like "3 2020" in cell A1 and run:

Sub INeedDates()
    Dim A1 As Range: Set A1 = Range("A1")
    Dim d As Date, i As Long

    i = 2
    arr = Split(A1, " ")
    d = DateSerial(arr(1), arr(0), 1)

    While CInt(Month(d)) = CInt(arr(0))
        If Format(d, "dddd") <> "Sunday" Then
            Cells(i, "A").Value = Format(d, "dddd")
            Cells(i, "B").Value = d
            Cells(i, "B").NumberFormat = "d/m/yyyy"
            i = i + 1
        End If
        d = d + 1
    Wend
End Sub

enter image description here

When I do (vaguely) similar things, I generally prefer to keep the Month and Year inputs separate, to keep it super simple for others to use later. (I've seen a surprisingly large number of people who have difficulties reliably entering dates properly in Excel.)

I recommend the following setup: (Sorry about the line breaks, some of these formulae are too long for one line in this table.)

╔════╦═════════════════════════════════════════════════╦════════════════════════════════╗
║CELL║                   DATA/FORMULA                  ║               COMMENTS         ║
╠════╬═════════════════════════════════════════════════╬════════════════════════════════╣
║    ║                                                 ║                                ║
║ A1 ║  "Month"                                        ║ Locked cell with bold, centered║
║    ║                                                 ║ text, yellow fill, and red bor-║
║    ║                                                 ║ der on left, right & top sides ║
║    ║                                                 ║                                ║
║ B1 ║  "Year"                                         ║ Locked cell with bold, centered║
║    ║                                                 ║ text, yellow fill, and red bor-║
║    ║                                                 ║ der on left, right & top sides ║
║    ║                                                 ║                                ║
║ A2 ║  11                                             ║ Unlocked cell with Data Valid- ║
║    ║                                                 ║ ation requiring a whole number ║
║    ║                                                 ║ from 1 to 12 (inclusive), right║
║    ║                                                 ║ justified with red border on   ║
║    ║                                                 ║ left, right & bottom sides     ║
║    ║                                                 ║                                ║
║ B2 ║  2018                                           ║ Unlocked cell with Data Valid- ║
║    ║                                                 ║ ation requiring a whole number ║
║    ║                                                 ║ from 1 to 12 (inclusive), left ║
║    ║                                                 ║ justified with red border on   ║
║    ║                                                 ║ left, right & bottom sides     ║
║    ║                                                 ║                                ║
║ A3 ║  "Day"                                          ║ Locked cell, formatted as      ║
║    ║                                                 ║ heading to below table         ║
║    ║                                                 ║                                ║
║ B3 ║  "Date"                                         ║ Locked cell, formatted as      ║
║    ║                                                 ║ heading to below table         ║
║    ║                                                 ║                                ║
║ A4 ║  =IF(B4="","",TEXT(B4, "DDDD"))                 ║ This shows the name of the day ║
║    ║                                                 ║ of the date found in cell B4   ║
║    ║                                                 ║                                ║
║ B4 ║ =IF(WEEKDAY(DATE(B2, A2, 1))-1, DATE(B2, A2, 1),║ This picks the date of the 1st ║
║    ║  DATE(B2, A2, 2))                               ║ day of the month chosen above, ║
║    ║                                                 ║ unless it's a Sunday, then it  ║
║    ║                                                 ║ is the following day (Monday)  ║
║    ║                                                 ║                                ║
║ A5 ║  Copy A4 to these cells                         ║ As you copy A4 to these cells  ║
║ to ║                                                 ║ Excel will automatically alter ║
║ A29║                                                 ║ each to reference the cell in  ║
║    ║                                                 ║ column B of this row           ║
║    ║                                                 ║                                ║
║ B5 ║ =IF(B4="","",IF(IF(WEEKDAY(B4+1)-1,B4+1,B4+2)>= ║ This picks the date after B4   ║
║    ║ EOMONTH(B4,0),"",IF(WEEKDAY(B4+1)-1,B4+1,B4+2)))║ unless it's a Saturday, then it║
║    ║                                                 ║ picks the next Monday, unless  ║
║    ║                                                 ║ it would go into the next month║
║    ║                                                 ║                                ║
║ B6 ║  Copy B5 to these cells                         ║ As you copy B5 to these cells, ║
║ to ║                                                 ║ Excel will automatically alter ║
║ B29║                                                 ║ each one to reference the cell ║
║    ║                                                 ║ above it instead of B4         ║
╚════╩═════════════════════════════════════════════════╩════════════════════════════════╝

And here's a screenshot of it in action:
Table in Excel displaying the days other than Sunday in November 2018 in both 'day name' and 'date' formats.

Not the slickest of a solution, but quick and easy. If you enter the first day of the month in cell A1, then in A2 add this formula....

=IF(TEXT(A1,"DDDD")="Sunday",A1+1,A1)
then A3
=IF(TEXT(A2+1,"DDDD")="Sunday",A2+2,A2+1)

You can then copy the formula down from there. It leaves Sundays out of your list. The only Sunday what will appear is the one you might enter in cell A1. I tested this for Sept 2019 as the first is Sunday. It returns

02/09/2019
03/09/2019
04/09/2019
05/09/2019
06/09/2019
07/09/2019
09/09/2019

Hope that helps.

Brad

  • instead of converting to string, using weekday would be a lot more efficient – phuclv Nov 9 at 1:48

Your Answer

Grungefreak is a new contributor. Be nice, and check out our Code of Conduct.
 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.