DAY Function in Excel: Day of the Month

📎 This article includes 1 downloadable practice file ↓

DateLevel: BeginnerAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Common errors and fixes
6. DAY in practice
7. Where you will use it
8. Worked example
9. Mistakes people make
10. Related functions
11. Continue learning

DAY returns 1–31 from a date. Pair it with YEAR and MONTH to rebuild or test dates.

Syntax

=DAY(serial_number)

Argument	What it means
serial_number	A real date, not text.

Examples

Example 1

=IF(DAY(A2)<=15, "First half", "Second half")

Split invoices by half-month.

Example 2

=DAY(EOMONTH(A2,0))

Number of days in that month.

Common errors and fixes

You see	Why, and the fix
#VALUE!	Text Excel cannot read as a date, e.g. 31/12/2018 on a PC set to US format.

DAY in practice

Where you will use it

• Day of month for due-date rules (e.g. pay by the 7th)
• Split dates into parts for reports
• Find invoices raised in the last week of a month

Worked example

Date	Formula	Result
04-Oct-2026	=DAY(A2)	4
28-Feb-2026	=DAY(A3)	28
31-Dec-2026	=DAY(A4)	31

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Applying DAY to text dates	Convert text to real dates first
Expecting the weekday	DAY gives the day number; WEEKDAY or TEXT(date,"dddd") gives the weekday

Related functions

MONTH · YEAR · EOMONTH

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Date & Time guides →

TIMEVALUE Function in Excel: Text Time to Real TimeDateLevel: IntermediateAvailable in: Excel 2007+TIMEVALUE turns text like "2:45 PM" into a real time…DATEVALUE Function in Excel: Convert Text Dates Into Real DatesDATEVALUE turns a date written as text into a real date number. It follows…MINUTE Function in Excel: Minute From a TimeDateLevel: BeginnerAvailable in: Excel 2007+MINUTE returns 0–59 from a time.Syntax=MINUTE(serial_number)ArgumentWhat it.SECOND Function in Excel: Second From a TimeDateLevel: BeginnerAvailable in: Excel 2007+SECOND returns 0–59. Mostly used to strip seconds from machine.

📎 Practice files for this article

• 📗
  Practice workbook: Dates & times22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.