VBA code to get the day name from a date

8

In this guide, we’ll provide a step-by-step approach to implementing a VBA code to get the day name from a date, demonstrate how to apply it to both single dates and lists of dates, and explain the code with examples to help you understand its functionality. Additionally, we will include customization options and highlight the benefits of using VBA for this purpose.

I. Why Use VBA to Get Day Name?

• Automation: Automates repetitive tasks involving date processing.
• Flexibility: Extracts day names dynamically for single or multiple dates.
• Customization: Offers control over the output format and integration with other tasks.

II. VBA code to get the day name from a date

1. VBA Code: Get Day Name from a Single Date

Sub GetDayNameFromDate()
    Dim inputDate As Date
    Dim dayName As String

    ' Read the date from cell A1
    inputDate = Sheet1.Range("A1").Value

    ' Extract the day name
    dayName = Format(inputDate, "dddd")

    ' Display the result
    MsgBox "The day name is: " & dayName

    ' Write the day name to cell B1
    Sheet1.Range("B1").Value = dayName
End Sub

How It Works:

Format(inputDate, "dddd"): Extracts the full day name (e.g., “Monday”).

Output:

• Displays the day name in a MsgBox.
• Writes the day name to Cell B1.

Example: Practical Use

Input Data:

Column A (Dates)
12/20/2024

Result After Running VBA:

• MsgBox: "The day name is: Friday"
• Column B: "Friday"

2. VBA Code: Get Day Names for Multiple Dates

If you have a list of dates in Column A and want to extract the day names into Column B, use the following code:

Sub GetDayNamesForList()
    Dim i As Long
    Dim lastRow As Long
    Dim inputDate As Date
    Dim dayName As String

    ' Find the last row in column A
    lastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row

    ' Loop through each date in column A
    For i = 1 To lastRow
        inputDate = Sheet1.Cells(i, 1).Value

        ' Check if the cell contains a valid date
        If IsDate(inputDate) Then
            dayName = Format(inputDate, "dddd")
            Sheet1.Cells(i, 2).Value = dayName ' Write day name to column B
        Else
            Sheet1.Cells(i, 2).Value = "Invalid Date"
        End If
    Next i

    MsgBox "Day names have been extracted!"
End Sub

How It Works:

IsDate(inputDate): Ensures the cell contains a valid date before processing.

Loop: Iterates through all rows in Column A.

• Writes the day names into Column B.
• Invalid Dates: Marks invalid entries with “Invalid Date” in Column B.

Example: Practical Use

Input Data:

Column A (Dates)
12/20/2024
01/01/2025
02/14/2025
Invalid Entry

Result After Running VBA:

Column A (Dates)	Column B (Day Names)
12/20/2024	Friday
01/01/2025	Wednesday
02/14/2025	Friday
Invalid Entry	Invalid Date

Customizing the Output Format

The Format function allows you to customize the day name format:

• Full Name: Use "dddd" (e.g., “Monday”).
• Abbreviated Name: Use "ddd" (e.g., “Mon”).
• Numeric Day: Use "d" for the day of the month.

III. Steps to Get Day Name from Date Using VBA

Step 1: Prepare Your Data

• Open your Excel workbook.

• Enter the dates you want to process in a column (e.g., Column A).

Step 2: Access the VBA Editor

Enable the Developer tab:

• Go to File > Options > Customize Ribbon.
• Check the Developer box.

Open the VBA Editor: Press Alt + F11 or click Visual Basic in the Developer tab.

Step 3: Insert a New Module

• In the VBA Editor, click Insert > Module.
• Copy and paste the following VBA code into the module.

IV. When to Use This VBA Code?

• To dynamically extract day names for data analysis or reporting.
• When working with a large dataset requiring quick day name extraction.
• For automating tasks involving date-based filtering or grouping.

---

Using VBA to extract day names from dates is a powerful way to enhance your Excel automation capabilities. Whether you’re processing a single date or an entire list, the provided VBA scripts make the task efficient and straightforward. By customizing the code, you can adapt it to suit your specific needs, ensuring a seamless workflow. Start implementing this today and take your Excel skills to the next level!

See more: Mastering VBA For Loops in Excel: A Complete Guide