Introduction
The DAY function in Excel extracts the day number from a valid date. For example, if a cell contains 15-Nov-2026, the DAY function returns 15. It is useful for analysing transaction dates, preparing attendance records, managing employee information, and organising business reports.
In this tutorial, you will learn the DAY function syntax, understand how it works, and practise it with simple examples.
1. What Is the DAY Function in Excel?
The DAY function extracts the day of the month from a given Excel date and returns it as a number between 1 and 31.
Suppose a company has an employee joining date:
| Joining Date |
|---|
| 15-Nov-2026 |
You want to extract only the day number.
Use:
=DAY(A2)
Expected Result: 15
Excel reads the date in A2 and returns its day component.
Remember: DAY returns only the day number. It does not return the month or year.
2. DAY Function Syntax
=DAY(serial_number)
Let’s understand the argument.
| Argument | Meaning |
|---|---|
| serial_number | A valid Excel date from which you want to extract the day. |
For example:
=DAY("25-Dec-2026")
Result: 25
The DAY function requires only one argument.
3. How Does the DAY Function Work?
Excel stores valid dates internally as serial numbers. When you provide a date to DAY, Excel reads that date and extracts its day component.
Suppose cell A2 contains 20-Aug-2026.
=DAY(A2)
Excel reads the date as follows:
| Date Component | Value |
|---|---|
| Day | 20 |
| Month | 8 |
| Year | 2026 |
Final Result: 20
The original date remains unchanged. Only the day number is returned.
Important Points to Remember
- DAY returns a number between 1 and 31 for normal calendar dates.
- The first day of a month returns 1.
- The last day of a month may be 28, 29, 30, or 31.
- The input should be a valid Excel date.
- If Excel cannot recognise the supplied value as a date, it may return a
#VALUE!error. - The result is a number, not a complete date.
4. Practical DAY Function Examples
Question 1: Extract Day from a Date
Problem Statement
A company wants to extract the day number from an employee’s joining date.
Data
| Employee | Joining Date |
|---|---|
| Amit | 15-Nov-2026 |
Assume the joining date is in cell B2.
Excel Formula
=DAY(B2)
Expected Result
15
Explanation
The date in B2 is 15-Nov-2026.
The day component is 15, so Excel returns 15.
Concept Covered: Extracting the day from a date.
Question 2: Extract Days from Multiple Employee Joining Dates
Problem Statement
An HR department wants to identify the day number of every employee’s joining date.
Data
| Employee | Joining Date |
|---|---|
| Amit | 15-May-2022 |
| Neha | 10-Feb-2023 |
| Rahul | 20-Aug-2024 |
| Priya | 05-Jan-2025 |
| Karan | 12-Nov-2026 |
Assume joining dates are in column B.
Excel Formula
=DAY(B2)
Expected Results
| Employee | Joining Date | Day Number |
|---|---|---|
| Amit | 15-May-2022 | 15 |
| Neha | 10-Feb-2023 | 10 |
| Rahul | 20-Aug-2024 | 20 |
| Priya | 05-Jan-2025 | 5 |
| Karan | 12-Nov-2026 | 12 |
Explanation
Enter the formula in C2 and copy it down for the remaining employees.
Excel extracts the day number from each employee’s joining date.
Concept Covered: Extracting days from multiple employee records.
Question 3: Extract Day from Sales Transaction Dates
Problem Statement
A sales manager wants to identify the day number on which each transaction occurred.
Data
| Transaction ID | Transaction Date | Sales |
|---|---|---|
| T101 | 10-Mar-2024 | 25000 |
| T102 | 15-Jul-2024 | 30000 |
| T103 | 20-Jan-2025 | 45000 |
| T104 | 12-Sep-2025 | 50000 |
| T105 | 05-Nov-2026 | 60000 |
Assume transaction dates are in column B.
Excel Formula
=DAY(B2)
Expected Results
| Transaction ID | Transaction Date | Sales | Day Number |
|---|---|---|---|
| T101 | 10-Mar-2024 | 25000 | 10 |
| T102 | 15-Jul-2024 | 30000 | 15 |
| T103 | 20-Jan-2025 | 45000 | 20 |
| T104 | 12-Sep-2025 | 50000 | 12 |
| T105 | 05-Nov-2026 | 60000 | 5 |
Explanation
DAY reads each transaction date and returns its day number.
For example, the date 10-Mar-2024 returns 10, while 05-Nov-2026 returns 5.
Concept Covered: Extracting day numbers from sales records.
Question 4: Extract Day from Student Admission Dates
Problem Statement
A school wants to identify the day number of every student’s admission date.
Data
| Student | Admission Date |
|---|---|
| Rohan | 05-Apr-2023 |
| Simran | 12-Jun-2024 |
| Mohit | 18-Jul-2025 |
| Pooja | 20-Mar-2026 |
Assume admission dates are in column B.
Excel Formula
=DAY(B2)
Expected Results
| Student | Admission Date | Day Number |
|---|---|---|
| Rohan | 05-Apr-2023 | 5 |
| Simran | 12-Jun-2024 | 12 |
| Mohit | 18-Jul-2025 | 18 |
| Pooja | 20-Mar-2026 | 20 |
Explanation
The DAY function extracts only the day number from each student’s admission date.
Concept Covered: Extracting admission days from student records.
Question 5: Extract Day from a Date Entered Directly in the Formula
Problem Statement
A user wants to extract the day number from a specific date without referring to another cell.
Excel Formula
=DAY("25-Dec-2025")
Expected Result
25
Explanation
The date is entered directly inside the formula. Excel recognises the date and DAY returns 25.
Concept Covered: Providing a date directly to the DAY function.
Question 6: Extract Day from Project Completion Dates
Problem Statement
A company stores project completion dates and wants to identify the day on which each project was completed.
Data
| Project | Completion Date |
|---|---|
| Website Development | 18-Aug-2025 |
| Mobile Application | 10-Feb-2026 |
| Database Migration | 25-Nov-2026 |
| Software Testing | 15-Apr-2026 |
Assume completion dates are in column B.
Excel Formula
=DAY(B2)
Expected Results
| Project | Completion Date | Completion Day |
|---|---|---|
| Website Development | 18-Aug-2025 | 18 |
| Mobile Application | 10-Feb-2026 | 10 |
| Database Migration | 25-Nov-2026 | 25 |
| Software Testing | 15-Apr-2026 | 15 |
Explanation
The formula reads each completion date and returns only its day number.
For example, 18-Aug-2025 returns 18, while 25-Nov-2026 returns 25.
Concept Covered: Extracting project completion days.
Question 7: Extract Day from Dates Across Different Months
Problem Statement
A company has transaction records from different months. The manager wants to extract the day number from each date.
Data
| Record | Transaction Date |
|---|---|
| Record A | 01-Feb-2026 |
| Record B | 15-Apr-2026 |
| Record C | 28-Feb-2026 |
| Record D | 31-Dec-2026 |
Assume dates are in column B.
Excel Formula
=DAY(B2)
Expected Results
| Record | Transaction Date | Day Number |
|---|---|---|
| Record A | 01-Feb-2026 | 1 |
| Record B | 15-Apr-2026 | 15 |
| Record C | 28-Feb-2026 | 28 |
| Record D | 31-Dec-2026 | 31 |
Explanation
DAY extracts the day component regardless of the month or year.
For example, the first day of February returns 1, while the last day of December returns 31.
Concept Covered: Extracting day numbers from different months.
Student Practice Tasks
Try these tasks using the DAY function.
Task 1: Employee Joining Day
Extract the day number from every employee’s joining date.
Task 2: Sales Transaction Day
Create a separate column showing the day number of every sales transaction.
Task 3: Student Admission Day
Extract the day number from student admission dates.
Task 4: Project Completion Day
Display only the day number from each project’s completion date.
Task 5: Different Month Records
Extract day numbers from dates containing different months and years.
Final Student Challenge: Build an Employee Joining-Day Report
An HR department wants to prepare a report showing the day number of every employee’s joining date.
Employee Data
| Employee | Department | Joining Date |
|---|---|---|
| Amit | IT | 15-Mar-2021 |
| Neha | HR | 20-Jun-2022 |
| Rahul | Finance | 10-Jan-2023 |
| Priya | IT | 25-Aug-2024 |
| Karan | Sales | 05-Feb-2025 |
| Simran | HR | 18-Nov-2026 |
Your Challenge
- Add a new column named Joining Day.
- Extract the day number from every joining date.
- Apply the formula to all employees.
- Add two new employee records with different joining dates.
- Extract their joining day numbers as well.
Goal: Create an employee joining-day report using the DAY function.
Key Takeaways
- DAY extracts the day number from a valid Excel date.
- Its syntax is
=DAY(serial_number). - It requires only one argument.
- The result is between 1 and 31 for normal calendar dates.
- The result depends on the day, not the month or year.
- You can use cell references or recognised date values.
- DAY is useful for employee, student, sales, and project reports.
- The function does not change the original date.
Frequently Asked Questions (FAQs)
1. What is the DAY function in Excel?
The DAY function extracts the day number from a valid Excel date.
2. What is the syntax of the DAY function?
The syntax is =DAY(serial_number), where serial_number represents a valid date.
3. What does DAY return for 15-Nov-2026?
It returns 15 because the day of the month is 15.
4. Can DAY extract the day from a cell reference?
Yes. If A2 contains a valid date, =DAY(A2) returns its day number.
5. Does DAY return the weekday name?
No. DAY returns the day of the month, not the weekday name.
6. What is the maximum day number returned by DAY?
For normal calendar dates, the maximum day number is 31.
7. Why does DAY return a #VALUE! error?
This can happen when Excel cannot recognise the supplied value as a valid date.
8. Does DAY change the original date?
No. It only extracts and returns the day number. The original date remains unchanged.
9. Can DAY work with dates from different months and years?
Yes. It extracts the day number regardless of the month or year.
10. What does DAY return for 01-Jan-2026?
It returns 1 because the date is the first day of January.

