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.

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.

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.