In the last post we developed a macro to get the weekday name of a date. But then we had to use the format of the dates according to the regional settings. So in this lesson I will show you how to develop a more advanced solution, to get the day name of a date independent of regional settings. For that I will use two more functions called “Split” and “DateSerial” in addition to the “Format" function. Let’s consider this sample excel sheet. As specified in the top row of column A, dates should be added in the given format (ex - mm/dd/yyyy). However you can easily change the code to suit with the format you need.
Dates have been added to column A in "mm/dd/yyyy" format. Now our macro should write the day name in front of each date.
So let’s start the macro by declaring the variables.
Dim i As Long
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim ColA_Text As String
Dim CurrentDate As Date
Dim DayName As String
Assign the activesheet to the WS variable.
We have dates in multiple rows. Therefore we need a For Next statement to iterate through each row. As we have dates from row 2 to row 10 we can use the For Next statement as follows.
Next i
When looping through each row assign the date of column A to ColA_Text variable.
Now this variable holds the date as string. So we can extract day, month and year separately using the split function.
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)
Want to learn about split function. Read this post.
Now we have day, month and year in 3 separate variables. So we can use the DateSerial function to get the date correctly independent on the region user lives.
Next, use the “Format” function to get the day name like we did in the previous lesson.
Finally we can write the day name to column B of the worksheet.
Below is the completed code.
Dim WS As Worksheet
Dim i As Long
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim ColA_Text As String
Dim CurrentDate As Date
Dim DayName As String
Set WS = ActiveSheet
For i = 2 To 10
ColA_Text = WS.Range("A" & i).Text
iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)
CurrentDate = DateSerial(iYear, iMonth, iDay)
DayName = Format(CurrentDate, "dddd")
WS.Range("B" & i).Value = DayName
Next i
End Sub
And this is the output of the macro.
In the above example we used “mm/dd/yyyy” as the date format. But as I said you can easily change the code to suit other formats. Because you need to change only these three lines to suit with the format.
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)
For example if dates are written in “dd/mm/yyyy” format you can modify the above three lines as follows.
iMonth = Split(ColA_Text, "/")(1)
iYear = Split(ColA_Text, "/")(2)
