The WeekdayName function in Microsoft Excel returns a string that represents the day of the week when given a number between 1 and 7. The WeekdayName function is a Date/Time Function that is a built-in function in Excel. It can be used in Excel as a VBA function (VBA). You can use this function as a VBA function in macro code written in the Microsoft Visual Basic Editor. In this article, we’ll learn how to use the VBA WeekdayName Function with two suitable examples in Excel.
VBA WeekdayName Function Overview
▶ Function Objective
To get the specific day of the week, the VBA WeekdayName function is applied.
=WeekdayName(weekday, abbreviate, firstdayofweek)
▶ Arguments Explanation
|weekday||Required||A number between 1 and 7 represents the day of the week.|
|abbreviate||Optional||If the weekday name is to be abbreviated, this is a Boolean value.
● True – the abbreviated weekday name (i.e. “Sun”, “Mon”, “Tue”, etc.)
● False – Return the whole weekday name (i.e. “Sunday”, “Monday”, “Tuesday”, etc.)
|firstdayofweek||Optional||The Numeric value indicates the first day of the week. See the Backgrounds section for values.|
The following values can be used for the firstdayofweek argument.
|vbUseSystem||0||A number between 1 and 7 represents the day of the week.|
The VBA WeekdayName is not a built-in function in Excel. Only VBA code in Microsoft Excel can use the WeekdayName function. Let’s say, we have a dataset where several Employee’s Names and their Joining date are given in columns B and C respectively. We’ll use the function to convert the joining date into the name of the Day of the Week. Undoubtedly, we can do this easily by using the VBA WeekdayName function. Here’s an overview of the dataset for today’s task. We will use this dataset to explain the VBA WeekdayName function.
1. Finding Out the Full Name Format of a Day by Using the VBA WeekdayName Function
Unquestionably, finding out the full name format of the Day of the week by using the VBA WeekdayName function is an easy task. To find out the full name format of the Day of the week, follow the instructions below.
- First, you have a look at our dataset. We’ll apply the VBA WeekdayName function in cell D5 to convert the date 01/01/22 into Day.
- Now, from your Developer Tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic menu, a window named Microsoft Visual Basic Applications will appear in front of you.
- From the Microsoft Visual Basic Applications – VBA WeekdayName Function window, go to,
Insert → Module
- A new module pops up. Now, type the below VBA Code in the module. We have provided the code here, you can just copy-paste the code and use it in your worksheet.
Sub example_weekdayName() Range("D5").Value = WeekdayName(1, False, vbSaturday) End Sub
- After inserting the code, we need to run the code to get the positive integer value. For that, go to,
Run → Run Sub/UserForm
- Finally, When running the code is completed, go back to your worksheet and you’ll be able to convert the date 01/01/22 into Day. The day is Saturday which has been given below screenshot.
- How to Use VBA Rnd in Excel (4 Methods)
- Use the VBA Environ Function (4 Examples)
- How to Use VBA IsEmpty Function (5 Relevant Examples)
- Use VBA And Function in Excel (4 Examples)
- How to Unhide Top Rows in Excel (7 Methods)
2. Evaluating the Short Name Format of a Day by Using the VBA WeekdayName Function
After learning the full format of the day of the Week, we will learn the short Name format of the Day of the week. Let’s try evaluating the short name Day within the same code. Please, follow the steps below to learn!
- Similarly, insert the below code in the VBA WeekdayName Function The code is,
Sub example_weekdayName() Range("D5").Value = WeekdayName(1, True, vbSaturday) End Sub
- From the Microsoft Visual Basic Applications window, go to,
Run → Run Sub/UserForm
- After running the code, go back to your worksheet and you will be able to get Sat as the return of the function which has been given in the below screenshot.
Things to Remember
👉 If you use the VBA WeekdayName Function in a query, the firstdayofweek parameter must be a number value (from 0 to 7). In the VBA code, you can only utilize the constant equivalent (i.e. vbSunday to vbSaturday).
👉 You can perform this function in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000 only.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
I hope all of the suitable examples mentioned above to use the VBA WeekdayName Function will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.