What it does
DATE creates a valid Excel date from year, month and day numbers.
Syntax or pattern
=DATE(year, month, day)5 practical examples
Create a date from columns
Build a date from year, month and day fields.
=DATE(A2,B2,C2)Useful after imports.
First day of month
Return the first day for a year and month.
=DATE(A2,B2,1)Common in monthly reports.
First day of current year
Create Jan 1 of the current year.
=DATE(YEAR(TODAY()),1,1)Useful for YTD calculations.
Next month date
Add one month using DATE logic.
=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))Excel handles month overflow.
End of previous month base
Build a date used with EOMONTH.
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1Returns the last day before the current month begins.
Common mistakes to avoid
- Check whether dates are real Excel dates, not text.
- Be careful with weekends, holidays and time portions.
- Explain whether a result counts calendar days or workdays.
Related Excel examples
FAQ
Why does the date formula return a number?
Excel stores dates as serial numbers. Format the cell as a date to display it correctly.
Here are some ideas for you
Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.
- Excel formula booksSee ideas
Practice formulas with structured examples you can keep beside your desk.
- Excel shortcut guidesSee ideas
Build speed with keyboard shortcuts for selection, formatting and navigation.
- Numeric keypadsSee ideas
Helpful if you enter many numbers on a laptop or compact keyboard.
- External monitorsSee ideas
Useful for viewing large worksheets, formulas and reference tables side by side.
- Desk notebooksSee ideas
Sketch formula logic, report ideas and table structures before building.
- Laptop standsSee ideas
Make long spreadsheet sessions more comfortable and ergonomic.
Some links in this section may be affiliate links. Choose only what is useful for your own work.