The most useful Excel calendar date formula is =DATE(year,month,day), which combines a year, month, and day into a valid date. For example, with the year in A2, month in B2, and day in C2, enter =DATE(A2,B2,C2); Excel returns the date represented by those three values.
What “calendar date formula” can mean
People use “calendar date formula” to mean a few related things: creating a date from separate parts, calculating a date a certain number of days away, finding the beginning or end of a month, or laying out a monthly calendar. Excel has different formulas for each task, so there is no single best formula for every calendar problem. The DATE function is the most reliable starting point when you need to construct a specific date.
The examples below are for Microsoft Excel for Microsoft 365 on Windows. The formulas also work in many earlier Excel versions. Microsoft 365 and Excel 2021 are required for the SEQUENCE function used in one calendar-grid example. Formulas use commas as argument separators; some regional settings require semicolons instead.
How Excel stores calendar dates
Excel stores dates as serial numbers and displays those numbers using a date format. In the default 1900 date system, each valid date corresponds to a number that advances by one for each calendar day. The time, if present, is stored as a fractional part of a day. This is why subtracting one date from another can give a number of days, and why a date formula may appear to return an unexpected number if the result cell is formatted as General or Number.
A date’s appearance is separate from its underlying value. For example, the same date can be displayed as 3-Nov-2026, November 3, 2026, or 11/3/2026, depending on the cell’s number format and regional settings. The displayed style does not change the date itself. Excel workbooks can also use either the 1900 or 1904 date system; those systems assign different serial numbers to the same date, so check compatibility when dates are moved between older workbooks or platforms.
Build a date with the DATE function
Use DATE when the year, month, and day are in separate cells, or when you want to type those parts directly into a formula. With a year in A2, a month number in B2, and a day number in C2, enter =DATE(A2,B2,C2). If A2 is 2026, B2 is 11, and C2 is 3, the result is November 3, 2026. Format the result cell as a date if Excel displays a serial number.
For a fixed date, the assumed cell layout is not needed because the formula contains the date parts directly. Enter =DATE(2026,11,3) to return November 3, 2026. This is safer than typing a date as text inside a formula because Excel can interpret text dates differently under different regional settings.
DATE also adjusts values that fall outside the usual month or day ranges. With no cell references required, =DATE(2026,13,1) returns January 1, 2027, because month 13 rolls into the following year. This behavior can be useful for date arithmetic, but it can also hide an incorrect month value in source data. If values should be restricted to months 1 through 12 or days valid for a particular month, validate the inputs separately.
Get today’s date or the current date and time
Use TODAY when a worksheet should show the current date and update as Excel recalculates. The formula assumes no input cells are needed: enter =TODAY() in the cell where you want today’s date. It returns the computer’s current date without a time value. It is useful for reports, age or deadline calculations, and labels such as “as of today,” but it is not a permanent timestamp; the displayed date can change on a later recalculation.
NOW returns the current date and time rather than only the date. With no input cells required, enter =NOW() in the cell where the current date and time should appear. Format the result to show both date and time if needed. Like TODAY, NOW is a volatile function: Excel may recalculate it when the workbook recalculates. If you need to preserve the current date as a fixed value, copy the result and use Paste Special > Values, or enter a date manually.
Add days, months, or working days
For ordinary calendar-day calculations, add or subtract a whole number from a date. With a start date in A2 and the number of days to add in B2, enter =A2+B2. If A2 is November 3, 2026, and B2 is 10, the result is November 13, 2026. To find a date 10 days earlier, use =A2-B2 with the same layout; the result is October 24, 2026. These calculations include weekends because they count calendar days.
EDATE moves a date by a specified number of months while retaining the day of the month when possible. With a starting date in A2 and the number of months to move in B2, enter =EDATE(A2,B2). If A2 is November 3, 2026, and B2 is 1, the result is December 3, 2026. A negative month count moves backward. When the original day does not exist in the destination month, EDATE returns the last valid day of that month; for example, moving one month forward from January 31 returns the last day of February.
To move forward by working days, use WORKDAY. With a start date in A2 and the number of working days to add in B2, enter =WORKDAY(A2,B2). If A2 is Tuesday, November 3, 2026, and B2 is 5, the result is Tuesday, November 10, 2026; Saturday and Sunday are skipped, and the starting day is not counted as one of the five days. WORKDAY can also take a range of holiday dates as an optional third argument, so specified holidays are skipped along with weekends.
Find the first or last day of a month
To find the first day of the month containing a date, use DATE with the year and month extracted from that date. With a date in A2, enter =DATE(YEAR(A2),MONTH(A2),1). If A2 contains November 3, 2026, the result is November 1, 2026. This approach is handy for monthly summaries because it works regardless of which day of the month is in A2.
EOMONTH is a direct way to find a month’s final date. With a date in A2 and the number of months to move in B2, enter =EOMONTH(A2,B2). If A2 is November 3, 2026, and B2 is 0, the result is November 30, 2026. Set B2 to 1 for the end of the following month, or -1 for the end of the preceding month. The result is a real date value, not a text label, so it can be used in comparisons and calculations.
A related pattern uses day zero of the next month. With the year in A2 and month number in B2, enter =DATE(A2,B2+1,0) to return the final day of the month specified by B2. For 2026 in A2 and 11 in B2, this returns November 30, 2026. EOMONTH is generally easier to read when you already have a date to work from.
Create a monthly calendar grid
A calendar grid needs a starting date that falls on the first day of the week shown in the top-left cell. The following formula makes a Sunday-first calendar. Put the year in B1 and the month number in B2; in A4, enter =DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),1)+1. If B1 is 2026 and B2 is 11, A4 returns Sunday, October 25, 2026, the Sunday at the start of the week containing November 1. Fill the formula across seven columns and down six rows, increasing each subsequent cell by one day, to display a complete calendar grid.
In Microsoft 365 or Excel 2021, SEQUENCE can generate all 42 dates at once. With the year in B1 and month number in B2, select A4 and enter =SEQUENCE(6,7,DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),1)+1,1). The result spills into a six-row-by-seven-column grid, beginning on the Sunday before or on the first of the month. Leave the cells in that spill area empty, or Excel will report a spill error. To make a Monday-first grid instead, use a weekday calculation that starts weeks on Monday; the WEEKDAY return-type argument controls which day is numbered first.
Format the grid as dates, then optionally hide dates outside the selected month with conditional formatting or a separate display formula. Keeping actual date values in the grid is usually better than entering day numbers as text: real dates can be sorted, compared, formatted, and used in other formulas.
Format dates and avoid common errors
If a formula returns a number such as 46329 instead of a familiar date, the calculation may be correct and the cell may simply be formatted as General. Select the result cells and choose Home > Number Format, then select Short Date or Long Date. For a custom display, open Format Cells with Ctrl+1 on Windows, choose the Number tab, and select Date or Custom. On Mac, Command+1 opens Format Cells in Excel; Excel for the web uses its ribbon controls and may not offer every desktop formatting option.
Common problems usually come from inputs or formatting rather than a broken date function:
- A date is left-aligned or behaves like text: Excel may have stored it as text, especially if it came from an import or uses a different regional date order. DATE can construct a reliable value from numeric year, month, and day parts.
- A result shows a serial number: change the cell’s number format to a date format. Do not multiply or divide the formula merely to make the number look different.
- A formula returns an unexpected month: check whether a month or day input is outside its normal range. DATE deliberately rolls overflow values into adjacent months or years.
- The date changes later: TODAY and NOW recalculate; they do not record a fixed entry time. Use a static value if the date must not change.
- Dates differ between workbooks: confirm that the workbooks use compatible date systems and that imported dates were interpreted using the intended regional convention.
Frequently Asked Questions
What is the basic formula to enter a date in Excel?
Use DATE with year, month, and day arguments. If the year, month, and day are in A2, B2, and C2, respectively, enter =DATE(A2,B2,C2). Format the result cell as a date if it displays as a serial number.
How do I make Excel show today’s date?
Enter =TODAY() in the cell where the date should appear. The value updates when Excel recalculates, so it is not a fixed timestamp.
How do I add one month to a date?
Use EDATE. With the starting date in A2, enter =EDATE(A2,1). It advances one month and adjusts to the last valid day when the destination month does not contain the original day number.
How do I calculate the number of days between two dates?
Subtract the earlier date from the later date. With the start date in A2 and end date in B2, enter =B2-A2. The result is the number of elapsed days; format the result as a number rather than a date.
Why does my date formula show a number?
Excel stores dates as serial numbers, so a cell formatted as General can show the underlying number. Apply a date format using Home > Number Format. If the number is not a plausible date serial, check the formula’s inputs and the cell references.
Related
- Best Way to Forecast in Excel: 5 Practical Options
- Best Free Excel Budget Templates for Monthly Planning
- Basic Project Timeline Template in Excel: A Simple Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Calculating QuarterThe simplest Excel formula for calculating the quarter from a date in cell A1 is =ROUNDUP(MONTH(A1)/3,0), which returns…
- IFERROR with XLOOKUP: Formula, Examples, and FixesWith the lookup value in E2, keys in A2:A5, and return values in B2:B5, use =IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"Not found"). It…
- IsNumber Function in Java: Methods to Check Numeric TextJava does not have a built-in, general-purpose isNumber() function. To check numeric text, choose a method that matches…
- VLOOKUP and XLOOKUP Examples: Formulas and Tips in ExcelFor an exact match, use VLOOKUP when the value you are searching for is in the first column…
- Excel FILTER Formula: Remove DuplicatesTo return filtered results without duplicates in Excel, combine FILTER with UNIQUE: FILTER selects the rows you want,…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.