Excel’s date code is the DATE function, which creates a real date from a year, month, and day. With literal inputs of 2024, 2, and 29, enter =DATE(2024,2,29) to return February 29, 2024; codes such as yyyy-mm-dd control how that date is displayed.
DATE Function Syntax and Quick Example
The DATE function’s general signature is DATE(year, month, day). The year, month, and day can be typed as numbers or supplied by cell references. DATE is useful when a date is assembled from separate fields, when another formula needs to construct a date, or when you want to avoid ambiguity in text dates such as 03/04/2025.
With the year in A2, month number in B2, and day number in C2, enter =DATE(A2,B2,C2) in D2. If A2 contains 2024, B2 contains 2, and C2 contains 29, the result is February 29, 2024. If D2 displays a number instead of a readable date, the value may still be correct; apply a date format as described below.
DATE returns an Excel date value, not a piece of date-shaped text. That distinction lets you sort dates chronologically, calculate the number of days between them, and use them in functions such as YEAR, MONTH, and DAY.
How Excel Stores Dates
Excel stores dates as serial numbers: whole-number steps represent days, and the number increases as time moves forward. In the standard 1900 date system used by most modern Windows workbooks, February 29, 2024 has serial value 45351. A date format makes that number appear as a calendar date. The value underneath remains numeric, which is why you can add days or compare dates.
Excel also supports a 1904 date system for compatibility with some older workbooks. The same calendar date has a different serial number in that system. Excel generally preserves the workbook’s selected system, but copying dates between workbooks using different systems can shift them by 1,462 days. If pasted dates appear years off, check whether the workbooks use different date systems before changing the values.
A time can be stored as a fraction of a day. For instance, noon is one-half of a day, so a cell can contain both a date and a time even if its format shows only the date. Changing a cell’s number format changes its display, not the stored serial value.
Build Dates from Separate Cells
Using DATE is often safer than joining values into a text string. If a form, CSV import, or worksheet has year, month, and day in separate columns, DATE combines them into a value Excel can calculate with. In the A2 year, B2 month, and C2 day layout, the formula =DATE(A2,B2,C2) produces a date in the row. Fill the formula down to create dates for the remaining records.
DATE also normalizes values that run past the usual calendar ranges. With literal inputs of year 2024, month 13, and day 1, =DATE(2024,13,1) returns January 1, 2025. With literal inputs of year 2024, month 2, and day 30, =DATE(2024,2,30) returns March 1, 2024. This rollover behavior can be useful for date arithmetic, but it can also conceal invalid source data. If an entered day must be rejected rather than adjusted, validate the input separately.
Pay attention to how two-digit years are interpreted. DATE treats year values from 0 through 1899 as an offset from 1900, so a year argument of 25 means 1925, not 2025. For clarity and dependable results, use four-digit years in imported data and formulas.
Excel Date Format Codes
A number format code determines which parts of a date are visible and how they are arranged. To set one, select the cells and press Ctrl+1 on Windows (Command+1 on Mac), open the Number tab, and choose Date for a standard option or Custom to enter a code. You can also use the number-format controls on the Home tab. On Excel for the web, use the number format options on Home; some desktop Format Cells controls may be presented differently.
m/d/yyyydisplays a date such as 2/29/2024.yyyy-mm-dddisplays 2024-02-29, a useful year-first layout for tables and exports.mmm d, yyyydisplays Feb 29, 2024.dddd, mmmm d, yyyydisplays Thursday, February 29, 2024.dd/mm/yydisplays 29/02/24; confirm that this order is clear to the people using the workbook.
In these codes, d represents the day, m the month, and y the year. One or two d or m characters show numeric values with or without a leading zero; three or four show abbreviated or full weekday/month names. Two y characters show a two-digit year, while four show a four-digit year. The letter m can also mean minutes in a time format, depending on the surrounding time codes. Use it in a date context for months, and consult the preview in Format Cells when building a combined date-and-time format.
Formatting does not convert a date into text or alter its calendar value. It only changes what Excel displays. Also, date separators and month/day order can be affected by regional settings, so a format that looks familiar on one computer may appear differently on another.
Useful Date Formula Patterns
Once a cell contains a real date, DATE can make common calendar calculations easier to read. For example, with a valid date in A2, =DATE(YEAR(A2),MONTH(A2),1) returns the first day of that date’s month. If A2 contains February 29, 2024, the result is February 1, 2024. This pattern is handy for grouping transactions by month.
With a valid date in A2, =EOMONTH(A2,0) returns the last day of the same month; for February 29, 2024, it returns February 29, 2024. EOMONTH can also move to another month: a positive offset selects a later month, and a negative offset selects an earlier one. It is useful for reporting periods and month-end schedules.
With a valid date in A2, =DATE(YEAR(A2),MONTH(A2)+1,1) returns the first day of the following month. For an A2 value of February 29, 2024, the result is March 1, 2024. These formulas rely on Excel’s date arithmetic, so format their result cells as dates rather than assuming the formula itself controls the display.
Text Dates, Locale, and Conversion
Imported dates can look like dates but remain text. Text values may fail to sort in calendar order, may not respond to date formatting, or may cause calculations to return errors. The ambiguity is especially noticeable with strings such as 04/05/2024: depending on regional conventions, that could mean April 5 or May 4. DATE avoids that ambiguity when the components are already separated into numeric year, month, and day fields.
If A2 contains a text date that Excel recognizes under the computer’s regional settings, =DATEVALUE(A2) converts it to a date serial. The result should be formatted as a date. DATEVALUE may not interpret a text string as intended if its order or language differs from the system’s locale, so verify the converted result before using it in a report. For consistently structured imports, use a controlled import process or split the components and rebuild the date with DATE.
When a cell already contains a numeric date serial, DATEVALUE is not necessary. Applying a date number format is the right fix for a serial that appears as a number. Conversely, changing the format of text does not turn it into a numeric date; conversion is required first.
Common Problems and How to Fix Them
- A number appears instead of a date: Select the cell and apply a Date or Custom number format. The underlying serial may already be correct.
- A formula displays as text: Check whether the cell is formatted as Text, or whether an apostrophe was entered before the equals sign. Change the format to General, then re-enter the formula.
- The date is shifted or unexpected: Check the month/day order in source text, the year value, and whether the DATE arguments have been placed in year-month-day order.
- A date rolls into another month: DATE deliberately normalizes out-of-range month or day values. Check the source fields if that rollover was not intended.
- The formula returns an error: Confirm that the referenced cells contain usable numeric components and that the year is within Excel’s supported date range. Negative years and years beyond 9999 cannot produce normal Excel dates.
Another frequent misconception is that a date format code changes the date itself. It does not: it changes presentation. If two cells show different text but contain the same serial value, their formats may differ. If they show the same date but calculations disagree, inspect the underlying values for hidden times or text instead of relying only on what is visible.
DATE Compared with TODAY and NOW
DATE constructs a particular calendar date from components. TODAY returns the current date according to Excel’s calculation environment, while NOW returns the current date and time. With no cell inputs required, enter =TODAY() in a cell to show the current date; it updates when the workbook recalculates. With no cell inputs required, =NOW() returns the current date and time and also updates on recalculation.
Use DATE for fixed records, deadlines, or dates derived from worksheet data. Use TODAY when a formula should respond to the current day, such as calculating an age in days or flagging dates that have passed. Use NOW when the time of recalculation matters. Because TODAY and NOW are recalculating functions, they are not suitable for recording an unchanging timestamp without taking an additional step to store the value.
Frequently Asked Questions
What is the Excel formula to enter a date?
Use DATE with year, month, and day arguments, such as =DATE(2024,2,29) for February 29, 2024. If the date is already a numeric Excel date, you can enter it directly and apply a date format.
What is the Excel code for YYYY-MM-DD?
In a Custom number format, enter yyyy-mm-dd. It displays a real Excel date in year-month-day order, but does not turn the value into text or change the date stored in the cell.
Why does Excel show a number instead of a date?
Excel dates are stored as serial numbers. Apply a Date format or a Custom format such as mmm d, yyyy to display the value as a calendar date. If the number does not change, the cell may contain text rather than a date serial.
How do I get the first day of a month?
With a date in A2, use =DATE(YEAR(A2),MONTH(A2),1). It returns the first day of the month containing A2, including when A2 is in a leap year.
Why does DATE turn month 13 into a date in the next year?
That is intentional normalization: DATE carries excess months or days into the next calendar period. It is useful in calculations, but check your input fields if the rollover signals a data-entry error.
Related
- Tableau Export to Excel: Steps, Limits, and Options
- Python pandas Excel Merge: Join Workbooks and Sheets
- Python Pandas Excel to CSV: Steps, Options, and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate an Excel List: Join Values With DelimitersTo concatenate an Excel list into one cell, use TEXTJOIN: with the values in A2:A6, enter =TEXTJOIN(", ",TRUE,A2:A6).…
- Excel PMT Function for Loans: Formula and ExamplesExcel’s PMT function calculates the regular payment for a loan with a constant interest rate and equal payments.…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- EDATE in Excel: Add or Subtract Months from a DateIn Excel, EDATE returns a date that is a specified number of months before or after a starting…
- IFERROR with AVERAGE in Excel: Formulas and ExamplesTo use IFERROR with AVERAGE in Excel, wrap the average calculation in IFERROR: with the values to average…
- Isnumber in C++: Standard Function or Platform Extension?No: C++ has no standard function named isnumber. To test whether one character is a decimal digit, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.