What it does
=DAY(A2) returns the day of the date in A2: 5 for 5 November 2026. It is the day of the month, not the day of the week; for Monday, Tuesday and so on use WEEKDAY.
Combined with EOMONTH, it tells you how many days a month has: =DAY(EOMONTH(A2,0)) returns the number of the last day of the month, which is the number of days in it. That is useful when rent or pay is prorated by days.
For the month and year use MONTH and YEAR; to rebuild a date from its parts use DATE. For the number of days between two dates you need their difference or DAYS, not DAY.
Syntax
=DAY(serial_number)=GÜN(tarih)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
serial_number | Required | The date, or the cell holding the date, whose day you want, e.g. A2. |
Examples
1Split payments into the first and second half of the month
Column C gives the day of the payment date; column D writes "First half" if that day is 15 or less and "Second half" otherwise.
=DAY(A2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Payment Date | Amount | Day | Period |
| 2 | 3-Oct-2026 | 12,400.00 | 3 | First half |
| 3 | 15-Oct-2026 | 3,850.00 | 15 | First half |
| 4 | 16-Oct-2026 | 7,200.00 | 16 | Second half |
| 5 | 28-Oct-2026 | 15,900.00 | 28 | Second half |
- C2 (filled down to C5)
=DAY(A2) - D2 (filled down to D5)
=IF(DAY(A2)<=15,"First half","Second half")
15 October is day 15 of the month and falls in the First half period; from 16 October on, payments count as Second half.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2First-month rent for a tenant moving in mid-month
Column D finds how many days the month has with EOMONTH. Column E multiplies the rent by the share of the month from the move-in day to the month end, counting the move-in day.
=DAY(EOMONTH(B2,0))| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tenant | Move-in Date | Monthly Rent | Days in Month | First Month Rent |
| 2 | Flat 3 | 10-Feb-2026 | 24,000.00 | 28 | 16,285.71 |
| 3 | Flat 5 | 21-Apr-2026 | 18,600.00 | 30 | 6,200.00 |
| 4 | Shop 1 | 5-Nov-2026 | 45,000.00 | 30 | 39,000.00 |
- D2 (filled down to D4)
=DAY(EOMONTH(B2,0)) - E2 (filled down to E4)
=C2*(D2-DAY(B2)+1)/D2
February 2026 has 28 days, so the tenant moving in on 10 February pays for the rest of the month: 16,285.71. For the flat taken on 21 April, the first month's rent is 6,200.00.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The date is stored as text that Excel cannot read.
Fix: Convert the values to real dates; to build them from parts, use DATE.
- Wrong duration
Cause: The time between two dates was worked out with
=DAY(B2)-DAY(A2); once the month changes, the result makes no sense.Fix: For durations, subtract the dates directly (
=B2-A2) or use DAYS.
Good to know
- To separate payments made up to the 15th:
=IF(DAY(A2)<=15,"First half","Second half"). - Days left in the month:
=DAY(EOMONTH(A2,0))-DAY(A2).
In Nexell
Get Nexell to do it from a sentence
Add a column to the rent list that works out each tenant's prorated first-month rent from their move-in date
Convert to tableBring your rent list into the tool and type this. Nexell adds the prorated rent column with real Excel formulas and shows it in the preview; in the downloaded file, the amount changes when the move-in date or the rent does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find how many days a month has in Excel?
Use =DAY(EOMONTH(A2,0)); it finds the last day of the month of the date in A2 and returns its number. For February 2028 it returns 29.