1. Core Concept & Syntax
The DAY function in Microsoft Excel extracts the day of the month from a valid date serial number as an integer between 1 and 31.
=DAY(serial_number)
serial_number: A valid Excel date cell reference (e.g. A2) or formula (e.g. TODAY()).
Returns a numeric integer from 1 to 31 representing the day of the calendar month.
Returns the day number only. It does not return names like Monday or Friday.
Example:
2. 🔥 Live Interactive — Extract Day of Month
Task: Extract the day of the month from each employee's joining date using =DAY(B2):
| # | A (Employee) | B (Joining Date - Editable) | C (=DAY(B2)) Extracted Day |
|---|---|---|---|
| 2 | Amit | =DAY(B2) | |
| 3 | Priya | =DAY(B2) | |
| 4 | Rahul | =DAY(B2) | |
| 5 | Neha | =DAY(B2) |
3. 🔥 Live Practice — Day-Based Order Validation
Task: Extract the day number from each order date to identify orders placed after the 20th of the month:
| # | A (Order ID) | B (Order Date) | C (=DAY(B2)) Day | D (Timing Flag) |
|---|---|---|---|---|
| 2 | ORD101 | 2026-08-05 | — | — |
| 3 | ORD102 | 2026-08-12 | — | — |
| 4 | ORD103 | 2026-08-20 | — | — |
| 5 | ORD104 | 2026-08-28 | — | — |
4. 🔥 Practical Business Use — Payment Dates & Billing Cycles
Business Rule: Invoices due on or before the 10th of the month belong to the Early Cycle. Construct =IF(DAY(B2)<=10, "Early Cycle", "Regular Cycle"):
| Invoice | Due Date | Day of Month (=DAY) | Billing Cycle (=IF) |
|---|---|---|---|
| INV001 | 2026-09-05 | — | — |
| INV002 | 2026-09-10 | — | — |
| INV003 | 2026-09-25 | — | — |
| INV004 | 2026-09-30 | — | — |
5. Combining DAY() with IF Statements
Task: Tag employees whose joining date falls on or before day 10 of the month:
| Employee | Joining Date | Extracted Day | Status (=IF(DAY(B2)<=10...)) |
|---|---|---|---|
| Amit | 2024-01-15 | 15 | =IF(...) |
| Priya | 2025-06-20 | 20 | =IF(...) |
| Rahul | 2026-03-10 | 10 | =IF(...) |
| Neha | 2026-08-25 | 25 | =IF(...) |
6. 🔥 Live Data Analysis Across Different Months
Task: Extract the day of the month for every transaction across different calendar months to identify late-month transactions:
| TX ID | Transaction Date | Amount | Extracted Day (=DAY) | Analysis Flag |
|---|---|---|---|---|
| TX001 | 2026-01-02 | ₹25,000 | — | — |
| TX002 | 2026-02-08 | ₹31,000 | — | — |
| TX003 | 2026-03-15 | ₹42,000 | — | — |
| TX004 | 2026-04-22 | ₹38,000 | — | — |
| TX005 | 2026-05-29 | ₹55,000 | — | — |
7. DAY() vs Date Cell Formatting
Understand the critical difference between extracting a value versus changing its visual appearance:
Yields a standalone numeric integer (e.g. 15). Can be compared with > 20, filtered in PivotTables, and used in mathematical calculations.
Displays "15" on the screen, but underneath the cell still stores the full date serial number (e.g. 46250). Comparisons with <= 10 will fail!
8. 🔥 Real Data-Quality: Flag Final 10 Days of the Month
Task: In August (31 days), flag transactions falling in the last 10 days (August 22nd to 31st) using =IF(DAY(B2)>=22, "Last 10 Days", "Other"):
| Record ID | Date (August 2026 - Editable) | Extracted Day | Validation Flag |
|---|---|---|---|
| R001 | — | — | |
| R002 | — | — | |
| R003 | — | — | |
| R004 | — | — |
9. Important: Day Number vs Day of the Week
Never confuse DAY() with day-of-the-week functions:
•
DAY("2026-08-15") ➔ 15 (Day of month number).•
WEEKDAY("2026-08-15") ➔ 7 (Day of week integer representing Saturday).•
TEXT("2026-08-15", "dddd") ➔ "Saturday" (Text day name).10. 🔥 Debugging Challenge: Fix DAY() Mistakes
Diagnose and fix these common formula errors:
11. Real Business Scenario — First 10 Days Billing Audit
Task: The billing team needs to audit invoices issued during the first 10 days of the month:
| Invoice ID | Invoice Date | Amount | Extracted Day (=DAY) | First 10 Days Status |
|---|---|---|---|---|
| INV101 | 2026-08-04 | ₹35,000 | — | — |
| INV102 | 2026-08-09 | ₹48,000 | — | — |
| INV103 | 2026-08-16 | ₹22,000 | — | — |
| INV104 | 2026-08-25 | ₹67,000 | — | — |
| INV105 | 2026-08-30 | ₹51,000 | — | — |
12. 🔥 Live Final Challenge: Monthly Timing Distribution
Task: Extract days using =DAY(B2) to classify transactions into First 10 Days (1–10), Mid-Month (11–20), and Late Month (21+):
| # | A (TX ID) | B (Transaction Date - Editable) | C (Amount) | D (=DAY(B2)) Extracted Day | E (Timing Distribution Bucket) |
|---|---|---|---|---|---|
| 2 | TX001 | ₹25,000 | — | — | |
| 3 | TX002 | ₹31,000 | — | — | |
| 4 | TX003 | ₹42,000 | — | — | |
| 5 | TX004 | ₹38,000 | — | — | |
| 6 | TX005 | ₹55,000 | — | — | |
| 7 | TX006 | ₹62,000 | — | — |
13. Quick Check Assessment Quiz
Test your understanding of the DAY() formula and monthly billing cycles:
Excel DAY() Assessment Quiz
Test your mastery of day-of-month extraction, range rules, and IF logic.
1. What does the Excel formula =DAY(A2) return when A2 contains the date "15/08/2026"?
14. Accuracy & Production Best Practices
Key takeaways for building date-sensitive reporting templates:
- Always use DAY() for Calculations: Never rely on visual date formatting when writing logical rules.
- Account for Varying Month Lengths: February has 28/29 days; April, June, September, and November have 30 days; others have 31 days.
- Dynamic Today Day: Use
=DAY(TODAY())to extract the current day number of the ongoing month.