Skip to main content

Get the Day Name of a Date Independent of Regional Settings

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.

Add the dates in the given format

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 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

Assign the activesheet to the WS variable.

Set WS = ActiveSheet

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.

For i = 2 To 10

Next i

When looping through each row assign the date of column A to ColA_Text variable.

ColA_Text = WS.Range("A" & i).Text

Now this variable holds the date as string. So we can extract day, month and year separately using the split function.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

Want to learn about split function. Read this post.

Split Function

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.

CurrentDate = DateSerial(iYear, iMonth, iDay)

Next, use the “Format” function to get the day name like we did in the previous lesson.

DayName = Format(CurrentDate, "dddd")

Finally we can write the day name to column B of the worksheet.

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

Below is the completed code.

Sub GetDayName()

     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.

Macro output the day name independent of region settings

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.

iDay = Split(ColA_Text, "/")(1)
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.

iDay = Split(ColA_Text, "/")(0)
iMonth = Split(ColA_Text, "/")(1)
iYear = Split(ColA_Text, "/")(2)


Comments

Post a Comment

Popular posts from this blog

How to Add a Formula to a Cell Using VBA

In this lesson you can learn how to add a formula to a cell using vba. There are several ways to insert formulas to cells automatically. We can use properties like Formula, Value and FormulaR1C1 of the Range object. This post explains five different ways to add formulas to cells. Table of contents How to add formula to cell using VBA Add formula to cell and fill down using VBA Add sum formula to cell using VBA How to add If formula to cell using VBA Add formula to cell with quotes using VBA Add Vlookup formula to cell using VBA We use formulas to calculate various things in Excel. Sometimes you may need to enter the same formula to hundreds or thousands of rows or columns only changing the row numbers or columns. For an example let’s consider this sample Excel sheet. In this Excel sheet I have added a very simple formula to the D2 cell. =B2+C2 So what if we want to add similar formulas for all the rows in column D. So the D3 cell will have the formula
Post a Comment
Read more

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc. So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL” Sub DownloadFileFromURL() End Sub First we need to declare a few variables. Dim FileUrl As String Dim objXmlHttpReq As Object Dim objStream As Object Next assign the URL of the file to the FileUrl variables FileUrl = "URL of your file" For an example if your file URL is https://www.example.com/images/chart.jpg then you can assig
Read more

Fill a Listbox From an Array

In this lesson you will learn how to fill a Listbox from an array. Listboxes are a very useful control type available in the VBA toolbox. You can use the listboxes for search and view functions of the VBA programs. First let’s fill the listbox using a multidimensional array. After that we can try it with a one dimensional array. This is the sample Excel sheet I’m going to use for this lesson. And here is the listbox we are going to fill with the array. So we have data in three columns and fourteen rows. First, we are going to add this data to an array. After that we can add the array to our listbox in the VBA form. You might wonder why we need to add the data to an array. Because data can be directly added to the listbox from the worksheet using VBA. Yes it is possible. But here our objective is to learn how to add data to a listbox from an array. Because there are situations where we need to add the data which is not available in worksheets to listboxes using VBA. For
Post a Comment
Read more