Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Get the Day Name of a Date Using VBA

In this lesson you can learn how to get the weekday name of a date. Here is an example worksheet. Column A of this sheet has a list of dates. We are going to get the day name of each date into column B using a macro.

Sample worksheet containing dates

First let’s give our macro a name. I will name the macro as “GetDayName”.

Sub GetDayName()

End Sub

We need a few variables for this job.

Dim WS As Worksheet
Dim i As Long
Dim CurrentDate As Date
Dim DayName As String

Next I will assign Activesheet to the WS variable.

Set WS = ActiveSheet

We have dates from row 2 to row 10. So we need a For Next statement to iterate through each row.

For i = 2 To 10

Next i

Then when the program loops through each row we can assign the date to CurrentDate variable like this.

CurrentDate = WS.Range("A" & i).Value

Next, the day name can be obtained using Format function.

DayName = Format(CurrentDate, "dddd")

Finally we can write the result to column B

WS.Range("B" & i).Value = DayName

Below is the completed macro.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          CurrentDate = WS.Range("A" & i).Value
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the result produced by the macro.

Macro returned the day name for each date

In this example I entered the dates in dd/mm/yyyy format. Also I have used the same in regional settings. But if I send this file to a user whose regional date format is “mm/dd/yyyy” then this program will output incorrect results. So if you want to use this macro, then you should format the dates in column A similar to your regional settings.

Contact Form

Name

Email *

Message *