WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Weekday

VBA Weekday

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Weekday Function

Weekday in VBA is a date and time function which is used to identify the weekday of a given date provided it as an input, this function returns an integer value from 1 to 7 range, there is an optional argument provided to this function which is the first day of the week but if we do not provide the first day of the week then the function assumes Sunday as the first day of the week by default.

Can we tell the weekday number by looking at a particular date? Yes, we can tell the day number that week, depending upon the starting day of the week. In regular worksheet functions, we have a function called WEEKDAY in excel to tell the number of the week for a particular date. In VBA, too, we have the same function to find the same thing.

VBA Weekday

What does Weekday Function do?

The weekday function returns the provided date’s day number in the week. For example, if you have dates 01st April to 07th April and if you want to know the day of the date 05th April if the starting day of the week is from Monday, it is the 5th day.

To find this, we have the same function as “Weekday” in a worksheet as well as in VBA. Below is the syntax of the function.

VBA Weekday Formula

Date: For which date we are trying to find the weekday. This should be a proper date with the correct format.

[First Day of Week]: In order to determine the weekday of the provided Date, we need to mention what is the first day of the week. By default, VBA considers “Monday” as the starting day of the week. Apart from this, we can supply the below days as well.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Weekday Constant

Examples

You can download this VBA WeekDay Function Excel Template here – VBA WeekDay Function Excel Template

Example #1

To start the proceedings, let me start with a simple example first up. Now we will try to find the weekday for the date “10-April-2019”.

Step 1: Define the variable as String

Code:

Sub Weekday_Example1()

    Dim k As String

End Sub

VBA Weekday Example 1

Step 2: Assign value to the variable

Assign the value to the variable “k” by applying the WEEKDAY function.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday(

End Sub

VBA Weekday Example 1-1

Step 3: Enter Date in Function

The date we are testing here is “10-Apr-2019”, so pass the date as “10-Apr-2019”.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday("10-Apr-2019"

End Sub

VBA Weekday Example 1-2

Step 4: Show Value of Variable in MsgBox

By default, it takes the first day of the week as “Monday,” so ignore this part. Close the bracket. The next line shows the value of variable “k” in the VBA message box.

Code:

Sub Weekday_Example1()

    Dim k As String

    k = Weekday("10-Apr-2019")

    MsgBox k

End Sub

Wday Example 1-3

Ok, we are done.

If we run the code, we will get the result as “4” because starting from Sunday, the provided date (10-Apr-2019) falls on the 4th day of the week.

Note: My system’s starting day of the week is “Sunday.”

VBA Weekday example 1-4

Similarly, if you change the start day of the week, it keeps varying. Below is an example line for the same.

Code:

k = Weekday("10-Apr-2019", vbMonday)

‘This returns 3

k = Weekday("10-Apr-2019", vbTuesday)

‘This returns 2

k = Weekday("10-Apr-2019", vbWednesday)

‘This returns 1

k = Weekday("10-Apr-2019", vbThursday)

‘This returns 7

k = Weekday("10-Apr-2019", vbFriday)

‘This returns 6

k = Weekday("10-Apr-2019", vbSaturday)

‘This returns 5

k = Weekday("10-Apr-2019", vbSunday)

‘This returns 4

Example #2 – Arrive Whether the Date is on Weekend or Not

Assume you have a date like the below, and you want to find the next weekend date, then we can use the WEEKDAY function to arrive at the results.

Wday Example 2

We need to use WEEKDAY with IF condition and loops to arrive at the result. I have written the code for you to go line by line to get the logic.

Code:

Sub Weekend_Dates()

    Dim k As Integer

    For k = 2 To 9

        If Weekday(Cells(k, 1).Value, vbMonday) = 1 Then
             Cells(k, 2).Value = Cells(k, 1) + 5
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 2 Then
             Cells(k, 2).Value = Cells(k, 1) + 4
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 3 Then
             Cells(k, 2).Value = Cells(k, 1) + 3
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 4 Then
             Cells(k, 2).Value = Cells(k, 1) + 2
        ElseIf Weekday(Cells(k, 1).Value, vbMonday) = 5 Then
             Cells(k, 2).Value = Cells(k, 1) + 1
        Else
             Cells(k, 2).Value = "This is actually the weekend Date"
        End If

    Next k

End Sub

Wday Example 2-1

This will arrive at the results below.

VBA Weekday Example 2-2

Look at the cells B6 & B7. We got the result as “This is actually the weekend date” because dates “04-May-2019” and “06-Apr-2019” are actually weekend dates, so no need to show the weekend date for weekend dates. By default, we get the result as this.

Recommended Articles

This has been a guide to VBA Weekday. Here we learn how to use the VBA weekday function to get the last day of the week with examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • IFERROR Function in VBA
  • VBA DateDiff Function
  • Excel Weekly Planner Template
  • What is VBA DATEVALUE Function?
  • VBA Paste Values
4 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA WeekDay Function Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More