DAY Function in Excel Tutorial

Excel Tutorial

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.

ArgumentMeaning
serial_numberA 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 ComponentValue
Day20
Month8
Year2026

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

EmployeeJoining Date
Amit15-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

EmployeeJoining Date
Amit15-May-2022
Neha10-Feb-2023
Rahul20-Aug-2024
Priya05-Jan-2025
Karan12-Nov-2026

Assume joining dates are in column B.

Excel Formula

=DAY(B2)

Expected Results

EmployeeJoining DateDay Number
Amit15-May-202215
Neha10-Feb-202310
Rahul20-Aug-202420
Priya05-Jan-20255
Karan12-Nov-202612

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 IDTransaction DateSales
T10110-Mar-202425000
T10215-Jul-202430000
T10320-Jan-202545000
T10412-Sep-202550000
T10505-Nov-202660000

Assume transaction dates are in column B.

Excel Formula

=DAY(B2)

Expected Results

Transaction IDTransaction DateSalesDay Number
T10110-Mar-20242500010
T10215-Jul-20243000015
T10320-Jan-20254500020
T10412-Sep-20255000012
T10505-Nov-2026600005

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

StudentAdmission Date
Rohan05-Apr-2023
Simran12-Jun-2024
Mohit18-Jul-2025
Pooja20-Mar-2026

Assume admission dates are in column B.

Excel Formula

=DAY(B2)

Expected Results

StudentAdmission DateDay Number
Rohan05-Apr-20235
Simran12-Jun-202412
Mohit18-Jul-202518
Pooja20-Mar-202620

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

ProjectCompletion Date
Website Development18-Aug-2025
Mobile Application10-Feb-2026
Database Migration25-Nov-2026
Software Testing15-Apr-2026

Assume completion dates are in column B.

Excel Formula

=DAY(B2)

Expected Results

ProjectCompletion DateCompletion Day
Website Development18-Aug-202518
Mobile Application10-Feb-202610
Database Migration25-Nov-202625
Software Testing15-Apr-202615

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

RecordTransaction Date
Record A01-Feb-2026
Record B15-Apr-2026
Record C28-Feb-2026
Record D31-Dec-2026

Assume dates are in column B.

Excel Formula

=DAY(B2)

Expected Results

RecordTransaction DateDay Number
Record A01-Feb-20261
Record B15-Apr-202615
Record C28-Feb-202628
Record D31-Dec-202631

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

EmployeeDepartmentJoining Date
AmitIT15-Mar-2021
NehaHR20-Jun-2022
RahulFinance10-Jan-2023
PriyaIT25-Aug-2024
KaranSales05-Feb-2025
SimranHR18-Nov-2026

Your Challenge

  1. Add a new column named Joining Day.
  2. Extract the day number from every joining date.
  3. Apply the formula to all employees.
  4. Add two new employee records with different joining dates.
  5. 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.