Home Excel VBA VBA code to get the day name from a date

VBA code to get the day name from a date

by Nguyen Duc Thanh
A+A-
Reset

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

    Related Posts

    Leave a Comment