Easy Excel Formula to Get Day from Date
When working with Excel, it’s common to handle dates and extract specific components, such as the day. Whether you’re creating reports or analyzing data, knowing how to pull the day from a date can save time and enhance accuracy. In this article, we’ll walk you through various Excel formulas and methods to retrieve the day from a date, making your work more efficient.
Understanding Excel Date Formats
Dates in Excel are stored as serial numbers, with the day, month, and year being part of this numeric value. Excel automatically converts entered dates into a numerical format. For example, January 1, 1900, is represented as 1, and January 2, 1900, is represented as 2.
Before extracting the day, it’s essential to ensure the date data is correctly formatted. Excel offers built-in tools to change and customize date formats, but understanding the underlying serial number system is key to using formulas effectively.
Basic Formula to Extract Day from a Date
The DAY function in Excel is the most straightforward way to retrieve the day from a date. The DAY function returns the day as a number from a date in the format of 1-31, depending on the date.
Syntax of the DAY Function
=DAY(serial_number)
- serial_number: This is the date from which you want to extract the day.
Example
If you have a date in cell A2, you can use the following formula to get the day:
=DAY(A2)
Result
If the date in A2 is 15/09/2023, the result will be 15.
Using TEXT Function to Get Day from Date
The TEXT function is another versatile method to get the day from a date, especially when you want the day displayed in a particular format. This function is often used to format dates or numbers as text strings.
Syntax of the TEXT Function
=TEXT(value, format_text)
- value: The date or cell reference containing the date.
- format_text: The format in which you want the day displayed.
Example 1: Getting Day Number
To extract the day number, you can use:
=TEXT(A2, "D")
This will return the day of the date as a number, similar to the DAY function.
Example 2: Getting Day Name (Short Format)
To extract the day name (e.g., “Mon,” “Tue”), you can use:
=TEXT(A2, "DDD")
If A2 has the date 15/09/2023, this formula will return “Fri”.
Example 3: Getting Day Name (Full Format)
To display the full day name (e.g., “Monday,” “Tuesday”), use:
=TEXT(A2, "DDDD")
For the same date 15/09/2023, this will return “Friday”.
Table Example for Different Formats
| Date | Formula | Result |
|---|---|---|
| 15/09/2023 | =DAY(A2) | 15 |
| 15/09/2023 | =TEXT(A2, "D") | 15 |
| 15/09/2023 | =TEXT(A2, "DDD") | Fri |
| 15/09/2023 | =TEXT(A2, "DDDD") | Friday |
Custom Formatting to Display Day from Date
Instead of using formulas, you can custom format your cells to display just the day or the day name from a full date. This method is helpful when you want to visually change the appearance of a date without altering the underlying data.
Steps to Custom Format a Date
- Select the date cells you want to format.
- Right-click and choose Format Cells.
- In the Format Cells dialog box, click on the Number tab.
- Choose Custom from the list.
- In the Type field, enter one of the following custom codes:
- “D”: For the day as a number (e.g., 15)
- “DDD”: For the day name (short form, e.g., “Mon”)
- “DDDD”: For the full day name (e.g., “Monday”)
- Click OK.
Example of Custom Formatting
If you have the date 15/09/2023 in a cell, applying the “DDD” format will display “Fri”, while applying the “DDDD” format will show “Friday”.
Extracting Day from Date Using Other Formulas
Beyond the DAY and TEXT functions, there are other Excel formulas that can help you manipulate dates and extract day information depending on your needs.
Using the WEEKDAY Function
The WEEKDAY function returns a number corresponding to the day of the week for a given date, where Sunday is 1 and Saturday is 7.
Syntax of the WEEKDAY Function
=WEEKDAY(serial_number, [return_type])
- serial_number: The date you want to analyze.
- return_type: Optional. Specifies the day on which the week starts. Use 1 for a week starting on Sunday, or 2 for a week starting on Monday.
Example
For the date 15/09/2023 in cell A2:
=WEEKDAY(A2, 2)
This will return 5, indicating that the date is a Friday (where Monday is 1 and Sunday is 7).
Using the CHOOSE Function with WEEKDAY
You can combine the WEEKDAY function with the CHOOSE function to return the day name directly.
=CHOOSE(WEEKDAY(A2,2), "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
This formula returns the name of the day for a given date, where WEEKDAY helps identify the day number and CHOOSE returns the corresponding day name.
Combining IF and DAY Functions
You might need to perform conditional operations based on the day extracted from a date. The IF function can be combined with the DAY function to return specific results depending on the day.
Example
Suppose you want to check if a date falls on the first day of the month. You can use:
=IF(DAY(A2)=1, "First Day", "Not First Day")
If the date in A2 is 01/09/2023, the formula will return “First Day”. Otherwise, it will return “Not First Day”.
How to Extract Day from Date Using VBA
If you’re comfortable using VBA (Visual Basic for Applications) in Excel, you can create a macro to extract the day from a date.
Example VBA Code
Function GetDayFromDate(dateValue As Date) As Integer
GetDayFromDate = Day(dateValue)
End Function
This custom function allows you to enter a date and receive the day directly.
Summary of Common Excel Formulas to Get Day from Date
Here’s a summary table of common formulas used to extract the day or day name from a date:
| Function | Description | Example Formula | Result |
|---|---|---|---|
| DAY | Returns day number (1-31) | =DAY(A2) | 15 |
| TEXT | Returns day as text or number | =TEXT(A2, "DDD") | Fri |
| WEEKDAY | Returns the day of the week as a number (1-7) | =WEEKDAY(A2, 2) | 5 (Friday) |
| CHOOSE & WEEKDAY | Returns day name using CHOOSE & WEEKDAY | =CHOOSE(WEEKDAY(A2,2), "Mon",...) | Friday |
| IF & DAY | Conditional check based on day number | =IF(DAY(A2)=1, "First Day", "...") | Not First Day |
Final Thoughts
Mastering how to extract the day from a date in Excel provides more control over your data analysis and reporting. Whether you use the DAY function, the TEXT function, or other formulas like WEEKDAY, each approach offers flexibility depending on your needs. Combining these functions with Excel’s powerful formatting options allows you to display date-related information in a way that suits your project or report.
FAQs
How do I get the day from a date in Excel?
You can use the DAY function to extract the day number from a date. The formula is =DAY(A2), where A2 is the cell with the date.
Can I display the full day name in Excel from a date?
Yes, you can use the TEXT function to display the full day name. Use the formula =TEXT(A2, “DDDD”) to display the full day name like ‘Monday’.
How can I get the short day name from a date?
You can use the TEXT function with the format code ‘DDD’ to get the short day name. For example, use =TEXT(A2, “DDD”) to get ‘Mon’ for Monday.
What is the difference between the DAY and TEXT functions?
The DAY function extracts the day number (1-31) from a date, while the TEXT function can extract the day and display it in various formats, including the day name.
How can I find the weekday number in Excel?
You can use the WEEKDAY function to find the weekday number, where Sunday is 1 and Saturday is 7. For example, use =WEEKDAY(A2, 2) to start the week on Monday.
Can I create a custom function to get the day from a date using VBA?
Yes, you can use VBA to create a custom function. The code would be: Function GetDayFromDate(dateValue As Date) As Integer. This allows you to return the day number from a date.
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.