To calculate a date in Excel, enter a starting date in one cell and use a formula such as =A2+7 to return a date seven days later. For a date based on a number of days in another cell, use =A2+B2. Excel stores dates as numbers, so you can add days, subtract dates, or use date functions such as EDATE to calculate a future or past date. First make sure your starting value is a real Excel date, not text.
What You’ll Need
- A worksheet in Excel for Microsoft 365 on Windows. The same basic date formulas work in Excel for Mac and Excel for the web, though some ribbon labels or locations may differ slightly.
- A starting date entered in a cell, plus any number of days, months, or years your calculation needs. If you are calculating working days, you may also need a list of holidays.
- A destination cell for the result. Format it as a date if Excel displays a serial number instead.
Step-by-Step
- Enter a starting date. Choose a cell, such as A2, and enter a date Excel can recognize. For example, type
1-Jun-2026rather than a potentially ambiguous date such as6/1/26. The exact formats Excel recognizes depend on your computer’s regional settings. To check that Excel has treated the entry as a date, select the cell and look at the Number Format dropdown on the Home tab. You can also temporarily change the cell to General; a recognized date will display as a number. Change it back to a date format afterward. - Decide what you want to calculate. A date formula can add or subtract calendar days, find the number of days between two dates, move by months, or skip weekends. Decide whether your answer should include the starting date. For example, the number of elapsed days from Monday to Tuesday is one, while an inclusive count of both dates is two. This choice affects the formula you use.
- Add or subtract calendar days. With a starting date in A2 and a number of days in B2, enter
=A2+B2in C2. If A2 contains 1-Jun-2026 and B2 contains 7, the result is 8-Jun-2026. To calculate a date seven days earlier, use=A2-7. You can type a number directly into a formula, but keeping the number in its own cell makes it easier to change the calculation later. Calendar-day addition counts Saturdays and Sundays. - Calculate the days between two dates. With the earlier date in A2 and the later date in B2, enter
=B2-A2in C2. If A2 is 1-Jun-2026 and B2 is 10-Jun-2026, the result is 9 days. The formula returns elapsed days and does not count both endpoints as separate days. To count both the start and end dates, use=B2-A2+1; the same example returns 10. Make sure the later date is in B2 if you want a positive result. - Calculate a date a number of months away. With a starting date in A2 and the number of months to move in B2, enter
=EDATE(A2,B2)in C2. If A2 is 15-Jan-2026 and B2 is 2, the result is 15-Mar-2026. Use a negative number in B2 to move backward. EDATE handles months of different lengths: moving one month forward from 31-Jan-2026 returns 28-Feb-2026, the last valid day of February. If you need the last day of the target month no matter what day the starting date uses, use EOMONTH instead. With the starting date in A2 and the number of months in B2, enter=EOMONTH(A2,B2); for 15-Jan-2026 and 1, the result is 28-Feb-2026. - Skip weekends when calculating a working-day deadline. With the start date in A2, the number of working days to add in B2, and holiday dates in F2:F10, enter
=WORKDAY(A2,B2,$F$2:$F$10)in C2. For a Monday start date of 1-Jun-2026 and 5 working days, the result is 8-Jun-2026, because WORKDAY does not count the start date and skips Saturday and Sunday. The dollar signs keep the holiday range fixed if you copy the formula down. If you have no holidays to exclude, use=WORKDAY(A2,B2). WORKDAY uses Saturday and Sunday as the weekend by default; use WORKDAY.INTL if your weekend pattern differs. - Build a date from separate year, month, and day values if needed. With the year in A2, month number in B2, and day number in C2, enter
=DATE(A2,B2,C2)in D2. If the cells contain 2026, 6, and 1, the result is 1-Jun-2026. DATE is useful when dates arrive in separate columns or when you need to construct a date reliably. It also normalizes values: a month number beyond 12 rolls into the next year, so check your source values if that is not intended. - Format and fill the result. Select the formula cell, then choose Home > Number Format dropdown > Short Date, or select Long Date to show the weekday and full month name. The formula result may otherwise appear as a number such as 46200; that is Excel’s date serial, not necessarily a failed calculation. To apply the same calculation to more rows, drag the fill handle at the lower-right corner of the formula cell. Check that relative references move as intended, and keep fixed ranges such as a holiday list absolute with dollar signs.
Common Mistakes
Entering a date as text. A date that looks right may be stored as text, particularly when it was copied from a website or imported from another system. Adding a number to text can produce a #VALUE! error or an unexpected result. Re-enter the value in a format Excel recognizes, or use DATE to build it from numeric year, month, and day values. Changing the cell’s appearance with date formatting does not convert text into a real date.
Confusing elapsed days with inclusive days. Subtracting the start date from the end date gives the time between them. It does not count both endpoints as full days. For a task lasting from June 1 through June 10 when both dates count, add one to the difference. For an age, gap, or elapsed-time calculation, adding one is usually not appropriate. Decide the counting rule before choosing the formula.
Expecting ordinary addition to skip weekends. Adding five to a date adds five calendar days, not five working days. Use WORKDAY for a Monday-to-Friday schedule, and include an accurate holiday list if applicable. A holiday list should contain actual Excel date values; text entries may not be excluded as expected.
Overlooking cell formatting or the starting date. A serial number can be a correctly calculated date shown in General format. Conversely, a formula can look plausible while using the wrong input cell or reversed date order. Check the formula bar and confirm the cells it references before changing the formula.
Assuming every month has the same number of days. Adding 30 days is not the same as adding one month. Use EDATE for a month-based shift and EOMONTH when the target should be the month’s final day. These functions account for varying month lengths and leap years.
Tips
Keep inputs and results in separate cells. For example, place the start date in column A, the number of days to add in column B, and the calculated date in column C. This makes assumptions visible and lets you update an input without editing the formula. Add clear headings such as Start date, Days to add, and Due date so another person can understand the sheet.
Use a consistent date format throughout the workbook. Short Date is compact, while Long Date can make a result easier to read in a report. Formatting changes how a date appears, not the underlying value used by the calculation. If a column contains dates and times, remember that the time is stored as a fraction of a day and may affect subtraction results. Apply a date-and-time format if you need to see that part of the value.
For a changing current date, enter =TODAY() in a cell to display today’s date, then use that cell in other calculations. TODAY updates when Excel recalculates the workbook, so it is not suitable for recording a fixed date that must never change. For a date that should remain fixed, enter the date as a value instead of using TODAY. In Excel for Windows, Ctrl+; inserts the current date as a fixed value; on a Mac, the equivalent is Control+;.
When copying formulas, inspect the references in the first few rows. A reference such as A2 changes to A3 as you fill down, which is usually useful for row-by-row calculations. A fixed holiday range should remain unchanged, so use absolute references such as $F$2:$F$10. Press F4 while editing a cell reference on Windows to cycle through reference types; on many Mac keyboards, use Command+T or Fn+F4 depending on the keyboard and Excel settings.
If a workbook will be shared internationally, avoid ambiguous typed dates and document the intended display format. A value such as 4/5/2026 can mean April 5 or May 4 under different regional conventions. A clear display such as 5-Apr-2026 helps people read the result, although the date’s underlying value is what formulas calculate with.
Troubleshooting
The cell shows a number instead of a date: Select the result cell and choose Home > Number Format dropdown > Short Date. If it changes into a sensible date, the calculation was likely correct and only the display format needed attention. If the date is far outside the expected range, inspect the inputs and formula references.
The formula returns #VALUE!: Check whether the starting date or day count is stored as text. Re-enter the date in a recognized format and make sure the amount being added is numeric. If the values came from an import, select the cells and review their formats, but remember that changing the format alone will not convert text. DATE can help when the year, month, and day are in separate numeric cells.
The result is one day off: Confirm whether you need elapsed days or an inclusive count. Also check whether one of the inputs contains a time value that is hidden by date-only formatting. If you are calculating business days, verify that the start date and holiday range are correct and that you understand WORKDAY’s rule of not counting the start date as the first working day.
The result does not update after an input changes: Check that Formulas > Calculation Options is set to Automatic. If calculation is set to Manual, select Formulas > Calculate Now or change the setting to Automatic. In Excel for Mac, the Calculation Options controls are on the Formulas tab as well. A TODAY formula can also appear unchanged until the workbook recalculates or its date changes.
WORKDAY returns an unexpected deadline: Confirm that the start date is a real date, the day count is the intended number of working days, and every holiday in the selected range is a valid date. Standard WORKDAY skips Saturday and Sunday. If your workweek uses different weekend days, use WORKDAY.INTL and consult its function arguments for the weekend pattern your schedule requires.
Frequently Asked Questions
How do I add 30 days to a date in Excel?
With the starting date in A2, enter =A2+30 in another cell. Excel returns the date 30 calendar days later, including weekends and holidays. Format the result cell as a date if it appears as a serial number.
How do I add one month instead of 30 days?
With the starting date in A2, use =EDATE(A2,1). This advances by one calendar month, adjusting to the last valid day when the target month is shorter. Thirty days and one month are not always the same interval.
How can I calculate a date that excludes weekends?
Use WORKDAY with a start date and the number of working days to add. Include an optional range of holiday dates if those days should also be skipped. By default, the function treats Saturday and Sunday as weekends.
Why does Excel show a five-digit number for my date?
Excel stores dates as serial numbers so it can calculate with them. Select the cell and apply Short Date from the Number Format dropdown on the Home tab. The number should then display as a calendar date.
Can I use a formula to show today’s date?
Yes. Enter =TODAY() in a cell. It updates when Excel recalculates, so use a fixed date value instead if you need to preserve the date on which a record was created.
What is the difference between TODAY and NOW?
TODAY returns the current date without a time. NOW returns the current date and time, which can affect calculations even when the cell is formatted to show only the date. Use the function that matches what the worksheet needs to represent.
Related
- How to Add a Checkbox in an Excel Column
- How to Add a Checkbox in Excel Without the Developer Tab
- How to Add a Yes or No Checkbox in Microsoft Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- Concat Excel Strings: Formulas and Practical ExamplesTo concatenate strings in Excel, join cell values with the ampersand operator (&) or use CONCAT; use TEXTJOIN…
- Excel Formula for Dividing Two Cells: ExamplesTo divide two cells in Excel, use a forward slash in a formula: with the dividend in A1…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name…
- Excel AND and OR Functions Combined: How to Use ThemYes. You can combine Excel’s AND and OR functions by nesting one inside the other. For example, with…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.