VBA DAY FUNCTION

What is VBA Day Function?

In VBA, the Day function is used to extract the day component from a specified date. It takes a date as an argument and returns an integer representing the day of the month. See the example below:

Define a sub-procedure using the VBA Day function two digits and extracting the “DD” component from the date given. The result is printed in the Immediate tab, as shown.The date format is “MM/DD/YYYY.”

Syntax

The syntax to use the VBA Day function is as shown.

Day(date)

where,

• date: The variable holding the date value.

How to Use VBA Day Function?

You can learn how to utilize the VBA Day function by going through the example below.

Step 1: In the Excel Workbook, go to the “Developer” section in the Excel toolbar.

Under “Developer,” select “Visual Basic” to open the VBA Editor.

In the toolbar of the VBA Editor, select “Insert,” then go to “Module” to open a new module to code in.

Step 2: Create a subroutine to display the current day of the week.

Step 3: Initialize a Date variable to store the Date value.

Step 4: Assign today’s date to the variable declared earlier.

Step 5: Initialize an integer variable to store the “day” component of the date value.

Step 6: Call the VBA Day function on the date value and store it in an integer variable.

Step 7: Print the day of the week. By formatting the date value using the VBA Format function using the “DDDD” format. It will print the day of the week.

Step 8: Using the MsgBox function, print all three formats.

Code:

Sub AnotherExample()

    Dim currentDate As Date

    currentDate = Date

    Dim dayOfMonth As Integer

    dayOfMonth = day(currentDate)

    MsgBox “Today is ” & Format(currentDate, “DDDD”)

    MsgBox “Today is the ” & dayOfMonth & “th day of the month.”

End Sub

Step 9: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Module to run the program. It prints a Message Box with the different date formats in excel.

Now that you know how to use the Day function in Excel VBA, you can go through more examples below.

Examples

Learn how to use VBA Day function for various purposes.

Example #1

Using the VBA Day function’s two digits result, you can find out whether the day is an even number or an odd number. Take an input date, then find the day number, and check if it is divisible by 2 using the VBA Mod function.

Step 1: Create a subroutine to find whether the given day is an odd day or an even day.

Step 2: Define a Date variable to store the date value from the user.

Step 3: Get the date from the user and store it in the date variable defined above.

Step 4: Use an If-Else condition to check if the given date is valid or not. If it isn’t valid, declare an error Message box and Exit the sub so that the rest of the code doesn’t get executed.

Step 5: Define an integer variable to store the day component from the Date value.

Step 6: Call the VBA Date function on the date from the user and store the result from the VBA Day function into the integer variable.

Step 7: Using an If-conditional statement, check whether the day component is divisible by 2. If so, create a Message Box telling the user that the day is even.

Step 8: Define an Else-conditional statement to check whether it is an odd day. If a number is not divisible by 2, it is an odd number.

Code:

Sub ActionBasedOnDayFunction()

    Dim inputDate As Date

    inputDate = InputBox(“Enter a date (mm/dd/yyyy):”, “Date Input”)

    If Not IsDate(inputDate) Then

        MsgBox “Invalid date entered. Please try again.”

        Exit Sub

    End If

    Dim dayOfMonth As Integer

    dayOfMonth = day(inputDate)

    If dayOfMonth Mod 2 = 0 Then

        MsgBox “The day is even.”

    Else

        MsgBox “The day is odd.”

    End If

End Sub

Step 10: Run the above subroutine by clicking on the green arrow button on the VBA Toolbar.

From this, you can tell the user that the date they’ve input in is an even day.

Example #2

Suppose you have a table containing dates. You want to find the day number along with the day of the week the date is in. You can use the VBA Day and VBA Format functions to do so. Follow the steps below.

Step 1: Define the subroutine to find the day numbers the dates have and store them in adjacent columns.

Step 2: Define a range variable to store the size of the table.

Step 3: Set the range of the table where this subroutine will work.

Step 4: Define a for-loop running through the table and go through each cell in the range.

Step 5: Call the VBA Day function to find the day number and store it in the adjacent column or the second column using the Cells function.

Step 6: In the third column, print the day of the week using the VBA Format function set as “dddd” to print the weekday of the date given in the table.

Step 7: Continue the FOR-loop.

Code:

Sub FindAllDays()

    Dim rng As Range

    Set rng = Worksheets(“Sheet5”).Range(“A2:A7”)

    For Each i In rng

        Cells(i.Row, 2).value = day(i.value)

        Cells(i.Row, 3).value = Format(Cells(i.Row, 1).value, “dddd”)

    Next i

End Sub

Step 8: Click the green arrow button when you want to run the subroutine. Then go to “Sheet5” to see your results.

Example #3

Suppose you want to know whether the current day is a prime number or not. You can extract the day component using the VBA Day function and check whether it is a prime number.

By dividing the number 2 by the square root of the number, you can find out if it’s a prime number or not. If none of the numbers from 2 to the square root of the number are divisible, then it is a prime number; otherwise, it is a composite number.

Step 1: Define a sub-procedure to check if a given day is a prime number or not.

Step 2: Find the day component of today’s date and store it in an Integer variable.

Step 3: Declare a Boolean variable as an indicator to check whether the given number is a prime number or not.

Step 4: Check if the day is less than or equal to 1. If so, set the Boolean variable as False.

Step 5: Define an Else condition block if the day is greater than 1.

Step 6: Run a FOR-loop ranging from the number 2 to the square root of the number.

Step 7: If the given number is divisible by any number from the range, set the Boolean variable as False.

Step 8: End the If condition in the loop and exit the sub-procedure.

Step 9: Continue the for-loop. After the loop, end the If-Else condition from Step 4.

Step 10: Declare another If-condition to check if the Boolean variable is true.

Print a Message Box function noting the day and that it is a prime number.

Step 11: Define an Else condition if the Boolean variable is set as False.

Code:

Sub CheckPrimeDay()

    Dim currentDay As Integer

    currentDay = day(Date)

    Dim isPrime As Boolean

    isPrime = True

    If currentDay <= 1 Then

        isPrime = False

    Else

        Dim i As Integer

        For i = 2 To Sqr(currentDay)

            If currentDay Mod i = 0 Then

                isPrime = False

                Exit For

            End If

        Next i

    End If

    If isPrime Then

        MsgBox “The day of the month (” & currentDay & “) is a prime number!”

    Else

        MsgBox “The day of the month (” & currentDay & “) is not a prime number.”

    End If

End Sub

Step 12: Run the above subroutine to see if the current day is a prime number or not.

The current day is not a prime number.

Important Things To Note

• If dealing with time components, consider using DateValue to remove the time portion before applying the Day function.
• Store the result of the Day function in variables of Integer type since it returns an integer representing the day.
• Be aware that the Day function operates based on the system’s local time. Account for any timezone differences if your application involves multiple time zones.
• The Day function extracts the day of the month, not the day of the week. For the day of the week, use the Weekday function.
• The Day function is meant for date values, not time-only.
• If the goal is to display a formatted date, consider using string formatting functions or custom date formatting instead of relying solely on the Day function.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a Guide to VBA Day Function. Here we explain how to use VBA Day function using syntax along with examples & downloadable excel template. You can learn more from the following articles –

• VBA TODAY
• VBA WEEKDAY Function
• VBA String to Date