Skip to content

Excel function · Date & time

Excel DAY function

DAY returns the day of the month of a date as a number from 1 to 31. Use it to find how many days a month has or to check whether a date falls in the first half of the month.

In Turkish Excel: GÜN

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

English Excel=DAY(serial_number)
Turkish Excel=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

ArgumentRequiredDescription
serial_numberRequiredThe 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.

C2fx=DAY(A2)
Split payments into the first and second half of the month: example table
ABCD
1Payment DateAmountDayPeriod
23-Oct-202612,400.003First half
315-Oct-20263,850.0015First half
416-Oct-20267,200.0016Second half
528-Oct-202615,900.0028Second 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.

D2fx=DAY(EOMONTH(B2,0))
First-month rent for a tenant moving in mid-month: example table
ABCDE
1TenantMove-in DateMonthly RentDays in MonthFirst Month Rent
2Flat 310-Feb-202624,000.002816,285.71
3Flat 521-Apr-202618,600.00306,200.00
4Shop 15-Nov-202645,000.003039,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

Type this in Nexell

Add a column to the rent list that works out each tenant's prorated first-month rent from their move-in date

Convert to table

Bring 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.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free