How to Use the Day Function in Excel VBA (3 Examples)

While working in Microsoft Excel Visual Basic for Applications (VBA), we may feel the necessity of using only the day value rather than the entire date. To do that, the usage of the Day function comes into action. The Day function is a built-in function of Excel VBA in the Date/Time Functions category that can extract days only out of an entire date in two digits of date format. The Day function is a data and day type function that has only one argument or parameter. In this article, you will learn the syntax and usage of the Day function in Excel VBA with 3 relevant examples.


Download the Practice Workbook

You can download the Excel file from the link below and practice along with it.


Introduction to Day Function in VBA

  • Function Objective:

We use the Day function to extract only the day from a complete date format.

  • Syntax:

Day(date_value)

  • Arguments Explanation:
Argument Required/Optional Explanation
date_value Required A date from which you want to extract only the day value.
  • Return Parameter:

It returns a two-digit date ranging from 1 to 31.


3 Examples of Using Day Function in Excel VBA

Example 1: Use Day Function in Excel VBA to Show Current Day in a Pop-up Window

Suppose, you’re working with the data in Excel. But if you don’t need to use the whole date then you would like to use the Day function to separate only the day out of the date format.

In this section, we will discuss how we can retrieve the current day using the Day function in Excel. We can get to know today’s date using the Now function in Excel.

But the Now function will return the complete form of a date with day, month, and year. But most probably you don’t want that.

So all we can do is, use the Day and the Now function together in Microsoft Excel VBA. In that situation, the Now function will return the complete date format, and then the Day function will filter that out to extract only the Day out of the date format.

Now let’s get into the procedural steps,

❶ First of all, open Microsoft Excel.

❷ Then open a new worksheet on a new workbook in Excel.

❸ After that press the ALT + F11 to open the Excel VBA editor.

❹ Then select Insert from ribbon and hit Module.

❺ After that copy the following VBA code.

Sub CurrentDay()

Dim CurrentDay As Integer

CurrentDay = Day(Now)

MsgBox CurrentDay, vbInformation, "Current Day"

End Sub

❻ Now paste the VBA code and save it on Excel VBA editor.

Use DAY Function in Excel VBA to Show Current Day in a Pop-up Window

❼ After that press the F5 button to run the code.

At this point, the Macros window will pop up.

❽ Select the Sheet1.CurrentDay function in the Macros window and hit the RUN command.

Use DAY function in Excel VBA: Macros

❾ After that, you will see a small window called Current Day has popped up returning the current day which is 11.

Read more: How to Get the Current Date in VBA


Example 2: Use Day Function in Excel VBA to Show Current Day in Excel Worksheet

In the previous example, we retrieved the current day out of the standard day format using the Day function in Excel. Now, we all return the current day using the Day function in Excel directly to the Excel worksheet.

To do that, we’ve allocated cell C4 to store the current day. So, when we run the Day function in Excel, we will get the current day in cell C4.

Now follow the steps below to get the whole procedure,

❶ Press ALT + F11 to open the VBA editor in Excel.

❷ Open a new Module from the Insert ribbon as in the previous example.

❸ Copy the following code:

Sub CurrentDayOnSheet()

gCurrentDay = Now

Sheets("Second Example").Range("C4") = Day(gCurrentDay)

End Sub

❹ Now paste and save this code.

Use DAY Function in Excel VBA to Show Current Day in Excel Worksheet

❺ After that press the F5 button to run the VBA code.

When you’ve run the above VBA code, you will get the current day in cell C4 of the Excel worksheet.

Read more: How to Insert Current Date in Excel


Similar Readings


Example 3: Use Day Function in Excel VBA to Show Current Day Name

In this last example, we will show the day name in a pop-up window using the Day function in Excel VBA. When we execute the VBA code, a pop-up window called “Today is:” will appear. Where the day will appear.

To do this, follow the steps below:

❶ Press the ALT + F11 keys to open the VBA editor.

❷ Go to Insert ▶ Module.

❸ Then copy the following VBA code.

Sub GetCurDay()
    Dim x As Date
    x = Date
    MsgBox Format(x, "DDDD")
End Sub

❹ Now, paste and save this VBA code.

❺ After that press the F5 button to run the code.

After pressing the F5 button, you will see the following window appear, which shows the current day name. For example, Tuesday.

Read more: How to Get the Day of Week Using VBA


Things to Remember

📌 Use ALT + F11 Keys to open Excel VBA editor.

📌 Press F5 to run Excel VBA Code.


Conclusion

To sum up, we have shown 3 examples to help guide using the VBA Day function in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Further Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo