Calendar Math in Excel: Dates, Workdays, and Formulas

Formulas & functions · Updated November 21, 2026

Calendar math in Excel means using real date values and date functions to add or subtract days, find month boundaries, build calendars, and count working days. For example, with a date in A2, enter =A2+7 to get the date one week later; format the result as a date if Excel displays a number instead.

How Excel stores dates

Excel stores dates as serial numbers: whole numbers count days in a date system, and decimal fractions represent times within a day. That is why adding 1 to a date advances it by one day, while adding 0.5 advances it by 12 hours. The displayed appearance is controlled separately by the cell’s number format.

In a new Windows workbook, Excel normally uses the 1900 date system. It also retains a historical compatibility error that treats 1900 as a leap year, even though it was not. This means dates in modern spreadsheets work as expected, but calculations involving dates before March 1900 can be unreliable. A workbook can instead use the 1904 date system; dates in the two systems differ by 1,462 days. When exchanging older workbooks or copying date serials between files, check that the date systems match.

To check the setting in Excel for Microsoft 365 on Windows, go to File > Options > Advanced, then look under When calculating this workbook for Use 1904 date system. The corresponding setting is in Excel’s preferences or settings on Mac, though the menu arrangement can differ by release. Avoid changing a workbook’s date system casually: existing dates may shift in appearance or meaning.

Add or subtract days, weeks, and elapsed time

Ordinary arithmetic is often the clearest way to do calendar calculations. With a date in A2, adding 7 moves forward one week, subtracting 14 moves back two weeks, and subtracting an earlier date from a later date gives the number of elapsed days. If A2 contains March 2, 2026, =A2+7 returns March 9, 2026. If A2 is March 2 and B2 is March 9, the layout is start date in A2 and end date in B2; =B2-A2 returns 7.

Be clear about whether you need elapsed days or a count that includes both endpoints. From Monday through Friday, subtracting the dates gives four elapsed days, but there are five calendar dates in the inclusive span. For an inclusive calendar-day count, with the start date in A2 and end date in B2, use =B2-A2+1. That returns 5 for March 2 through March 6, 2026. This distinction matters for event schedules, coverage periods, and inventory plans.

Dates can also include times. If A2 is March 2 at 9:00 a.m. and B2 is March 2 at 3:00 p.m., the difference is 0.25 of a day, or six hours. With the start date and time in A2 and the end date and time in B2, =(B2-A2)*24 returns 6. Format the result as a number, not a date, to show the hours.

Move by months and find month boundaries

Adding a fixed number of days is not the same as adding a month: months have different lengths. Use EDATE when a date needs to move by a stated number of calendar months. With a starting date in A2, =EDATE(A2,1) returns the date one month later, adjusting to the last valid day when necessary. For example, one month after January 31, 2026 is February 28, 2026. EDATE is useful for recurring monthly reviews, due-date schedules, and anniversaries.

To find the final date of a month, use EOMONTH. With any date in A2, =EOMONTH(A2,0) returns the last day of that date’s month. Change 0 to 1 for the end of the following month or -1 for the end of the previous month. For a date in February 2024, the zero-month result is February 29 because 2024 is a leap year.

For the first day of a particular month, use DATE rather than typing a date as text. With the year in B1 and month number in B2, =DATE(B1,B2,1) returns the first day of that month. DATE also handles values outside the usual month range: a month number of 13 rolls into January of the next year. That behavior can be useful in formulas, but validate user-entered month numbers if they should be limited to 1 through 12.

Build a monthly calendar grid

A worksheet calendar is a date calculation plus formatting. First choose whether the week begins on Sunday or Monday. For a Monday-first calendar, calculate the Monday on or before the first of the month. With the year in B1 and month number in B2, =DATE(B1,B2,1)-WEEKDAY(DATE(B1,B2,1),2)+1 returns the grid’s starting date. For May 2026, it returns April 27, the Monday before May 1.

In Microsoft 365, SEQUENCE can spill a complete six-row, seven-column calendar. With the year in B1 and month number in B2, enter =SEQUENCE(6,7,DATE(B1,B2,1)-WEEKDAY(DATE(B1,B2,1),2)+1,1) in an empty cell. For May 2026, the grid begins April 27 and ends June 7. Apply a date number format such as d to show only day numbers, while retaining full dates for calculations. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; older versions can fill the first date and extend the grid by adding 1 across and down.

The grid includes dates from neighboring months so every week remains complete. To distinguish those dates, select the grid and use Home > Conditional Formatting > New Rule. You can create a rule based on whether a cell’s month differs from the selected month, then apply a muted font color. If you build a rule with a formula, make its cell references match the top-left cell of the selected grid and use a mixed or absolute reference for the month selector as appropriate. A calendar should still contain true date values; typing only day numbers loses the year and month needed for sorting and further calculations.

Count weekdays, workdays, and holidays

Calendar days include weekends; workday calculations generally exclude Saturday and Sunday. NETWORKDAYS counts working days between two dates and includes both endpoints when they are weekdays. With a start date in A2, an end date in B2, and a holiday date in C2, =NETWORKDAYS(A2,B2,C2:C2) returns 4 for March 2 through March 6, 2026, if March 4 is the holiday. The holiday argument can be a range containing multiple dates; keep those entries as actual Excel dates.

WORKDAY answers the reverse kind of question: what date falls a given number of working days before or after a starting date? With March 2, 2026 in A2, =WORKDAY(A2,5) returns March 9, 2026. The start date is not counted as the first workday; counting begins on the next eligible weekday. To exclude holidays too, with the same start date in A2 and holiday dates in C2:C3, use =WORKDAY(A2,5,C2:C3). Both WORKDAY and NETWORKDAYS offer an .INTL version for workweeks that do not follow the standard Saturday-Sunday pattern.

For a repeating shift pattern or a region with a different weekend, choose the weekend settings deliberately. WORKDAY.INTL and NETWORKDAYS.INTL allow a weekend code or a seven-character weekend pattern. Consult Excel’s function help for the code conventions before using one, and test a short date range whose expected working days you can verify manually. A holiday list is not automatically inferred from a country or calendar; you must supply the dates relevant to your organization.

Weekdays, week numbers, and leap years

WEEKDAY converts a date into a number representing its day of the week. With a date in A2, =WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday. The second argument controls the numbering system, so document it when others will maintain the workbook. This function can help identify weekend dates or label calendar columns.

Week numbers are less universal than they appear. With a date in A2, =WEEKNUM(A2,2) numbers weeks with Monday as the first day, but its week-one convention is not the ISO convention. For ISO week numbering, with the date in A2, use =ISOWEEKNUM(A2). Near New Year, the ISO week-year can differ from the calendar year: a date in early January may belong to the final ISO week of the previous year. If reports use week numbers, confirm the organization’s definition before comparing totals.

Leap years explain why a year is not always 365 days. Under the Gregorian calendar, a year divisible by 4 is usually a leap year, except century years that are not divisible by 400. Thus 2024 and 2000 were leap years, while 1900 was not. Excel’s DATE and EOMONTH functions account for leap days in ordinary modern date calculations, so formulas are safer than assuming February always has 28 days.

Formatting, text dates, and common errors

A date that looks wrong may be a formatting issue rather than a calculation error. If a cell displays a serial number, select it and choose a date format from Home > Number, or open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells. Conversely, a date format can make a number appear date-like, so check the underlying value and formula when a result seems unexpected.

Text that resembles a date is not always a usable date value. Imported entries such as 03/04/2026 can be ambiguous: depending on regional settings, they may mean March 4 or April 3, and they may remain text. Use a clear, consistent input convention and check whether Excel aligns or calculates the entries as dates. For imported data, the Text to Columns tool or Power Query can help convert text, but the correct interpretation depends on the source’s date order and locale.

  • Unexpected month shifts: use EDATE for month-to-month movement rather than adding 30 days.
  • Off-by-one counts: decide whether the count is elapsed or inclusive, then add 1 only when both endpoints should count.
  • Wrong weekday: confirm the cell contains a real date and check the WEEKDAY return-type argument.
  • Negative or strange serials: check for reversed start and end dates, text values, or a mismatch between workbook date systems.
  • Different regional interpretation: avoid ambiguous typed dates and verify imported values against the source.

Choose the right approach for your calendar

Use direct addition and subtraction for simple day intervals, EDATE for month-based schedules, EOMONTH for month-end deadlines, and NETWORKDAYS or WORKDAY for business-day planning. A visible calendar grid is useful for people scanning dates, but a structured table is often better for analysis: put one event or transaction on each row, store its date in a date column, and use filters or PivotTables to summarize by month. A grid is a presentation; a date column is usually the more flexible data structure.

For a small, fixed calendar, ordinary formulas and formatting are easy to inspect. Microsoft 365 and Excel 2021 also support dynamic-array functions such as SEQUENCE, which can generate a grid from one formula. Older versions need formulas filled into multiple cells. If a workbook must be shared widely, consider whether every recipient’s Excel version supports the functions used, and avoid relying on a spill formula if the target cells may contain other data.

Frequently Asked Questions

How do I add 30 days to a date in Excel?
With the starting date in A2, enter =A2+30. This adds 30 calendar days, not one calendar month; use EDATE when the requirement is a month-based interval.

How do I calculate the number of days between two dates?
With the start date in A2 and end date in B2, subtract the start from the end using =B2-A2. Add 1 if you need to count both dates in the range and both are meant to be included.

How can I calculate the last day of a month?
With a date in A2, =EOMONTH(A2,0) returns the final date of that month. Format the result as a date if Excel displays a serial number.

How do I count business days between dates?
Put the start and end dates in A2 and B2, and list holidays in C2:C20. Use =NETWORKDAYS(A2,B2,C2:C20) to count weekdays while excluding the listed holidays.

Why does Excel show a number instead of a date?
Dates are stored as serial numbers, so the cell may simply be using General or Number formatting. Apply a date format from Home > Number; if the value still seems wrong, check that the input is a real date rather than text.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.