Enter the Weekdays Mon – Fri in a Worksheet using VBA in Microsoft Excel

In this article, we will create a macro to extract the weekdays between two dates.

In this example, we have to specify start date and end date before running the macro. Macro picks the start date value from cell J8 and end date value from cell J9. After specifying the start and end date, macro can be executed by pressing the “Submit” button or pressing shortcut keys Alt + F8.

ArrowMainSheet

On running the macro, it will return weekdays between two dates on a new sheet. The newly inserted worksheet contains weekdays in column A, followed by the respective date in column B. Output date in column B will be in dd.mm.yy format.

Each Week will be followed by a blank row to differentiate between two consecutive weeks.

ArrowAfterRunningMacro

Logic explanation

In the macro, we have used Weekday function to differentiate between weekdays and weekends. Format function is used to display the date in the required format.

Weekday function

The Weekday function returns an integer value which represents the day of the week.

Syntax

Weekday( Date_Value, [First_Day_Of_Week] )

Date_Value specifies the date value for which you want to find the day of the week.

First_Day_Of_Week specifies which day of the week is to be considered as the first day of the week. It takes an integer value or the value chosen from the FirstDayOfWeek enumeration as input. If no value is specified, then FirstDayOfWeek.Sunday is used as default value.

Enumeration value	Integer value	Remarks
FirstDayOfWeek.System	0	First day of week specified in system settings
FirstDayOfWeek.Sunday	1	Sunday (default)
FirstDayOfWeek.Monday	2	Monday
FirstDayOfWeek.Tuesday	3	Tuesday
FirstDayOfWeek.Wednesday	4	Wednesday
FirstDayOfWeek.Thursday	5	Thursday
FirstDayOfWeek.Friday	6	Friday
FirstDayOfWeek.Saturday	7	Saturday

As seen from the code of the macro, we have used Weekday(i, 2) to specify Monday as first day of the week.

Format function

The Format function takes a date expression as input and returns it as a formatted string.

Syntax of Format function

Format(Date_Value,format)

Date_Value specifies the value of date data type.

Format uses string value to specify what type of date format is required.

The following table defines some popular characters which one can use to create required date/time formats:

Character	Description
d	displays the day as a number without a leading zero (1 – 31)
dd	displays the day as a number with a leading zero (01 – 31)
ddd	displays the day as an abbreviation (Sun – Sat)
dddd	displays the day as a full name (Sunday – Saturday)
w	displays the day of the week as a number (1 for Sunday through 7 for Saturday)
ww	displays the week of the year as a number (1 – 54)
m	displays the month as a number without a leading zero (1 – 12)
mm	displays the month as a number with a leading zero (01 – 12)
mmm	displays the month as an abbreviation (Jan – dec)
mmmm	displays the month as a full month name (January – december)
q	displays the quarter of the year as a number (1 – 4)
y	displays the day of the year as a number (1 – 366)
yy	displays the year as a 2-digit number (00 – 99)
yyyy	displays the year as a 4-digit number (100 – 9999)
h	displays the hour as a number without leading zeros (0 – 23)
hh	displays the hour as a number with leading zeros (00 – 23)
n	displays the minute as a number without leading zeros (0 – 59)
nn	displays the minute as a number with leading zeros (00 – 59)
s	displays the second as a number without leading zeros (0 – 59)
ss	displays the second as a number with leading zeros (00 – 59)

To leave a blank row at the end of each week, we have checked for Sundays using weekday function and increment the value of “StartingRow” variable by 1 so that the next row remains empty.

As seen in the code of the macro, we have used the Format function two times in different ways. Firstly, we have used Format(i, "ddd") to get the weekday and secondly, we have used Format(i, "dd.mm.yy") to get the date in dd.mm.yyyy format.

Code explanation

Dim NewWorksheet As Worksheet

Set NewWorksheet = Worksheets.Add

Above code is used to declare Worksheet object name as “NewWorksheet”. Worksheets.Add method is used to add the new worksheet in the Worksheets collection. Set statement is used to initialize the declared object with the newly inserted sheet.

For i = StartDate To EndDate

FOR loop is used to loop from start date to end date.

If Weekday(i, 2) < 6 Then

IF statement is used to check the condition and execute the code based on the condition. Above condition will check value return by the Weekday function. If it is less than 6, IF condition returns true and the code inside IF statement will execute. Otherwise, it will be skipped.

StartingRow = 1

StartingRow = StartingRow + 1

StartingRow variable is used to move through rows on the worksheet. At the starting, variable is initialized to first row. With each successful execution of IF statement, the variable value gets incremented by 1, moving to next row in the sheet.

Cells(StartingRow, 1)

Cells property is used to reference a particular cell on the worksheet. Cells(row_number,column_number) can be used to reference any cell on the worksheet by entering a particular row number and column number as parameters. In the code Cells(StartingRow, 1), 1 specifies the first column and variable “StartingRow” defines the row number.

One can easily understand the codes as I have included comments along with codes in the macro.

Please follow below for the code

Option Explicit

Sub ExtractWeekdays()

'Declaring two variables of Date data type
Dim StartDate As Date, EndDate As Date

'Declaring worksheet variable
Dim NewWorksheet As Worksheet

Dim StartingRow, i As Long

    
'Getting start date and end date values from worksheet
StartDate = Sheets("Macro").Range("J8").Value
EndDate = Sheets("Macro").Range("J9").Value

'Initializing the starting row number for output
StartingRow = 1

'Inserting new worksheet
Set NewWorksheet = Worksheets.Add


For i = StartDate To EndDate
    
    'Using Weekday method to check whether it is Weekday or weekand
    If Weekday(i, 2) < 6 Then
        
        'Inserting value on newly inserted worksheet
        'Format method is used for formatting the date value
        NewWorksheet.Cells(StartingRow, 2) = Format(i, "dd.mm.yy")
        NewWorksheet.Cells(StartingRow, 1) = Format(i, "ddd")
        
        'Updating StartingRow variable value for moving to next row
        StartingRow = StartingRow + 1
    End If
    
    'Inserting blank row for weekend
    If Weekday(i, 2) = 7 Then
        StartingRow = StartingRow + 1
    End If

Next i

Set NewWorksheet = Nothing

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Francis
September 10, 2018 at 2:32 am

Greetings, Sir!
There is an error in StartDate = Sheets("Macro").Range("B5").Value run-time error 9. Please can you write the code in writing the value of Saturday and Sunday to a cell.
Thank you!

Reply ↓