Calendar Day Formula in Excel: Dates, Weekends & More

Formulas & functions · Updated November 19, 2026

For the day number within a date, use =DAY(A2), where A2 contains an Excel date; for example, January 15 returns 15. To count calendar days between two dates, subtract the earlier date from the later one, or add 1 when you want to include both endpoints. These formulas count every date, including Saturdays, Sundays, and holidays.

What “calendar day” can mean in Excel

“Calendar day formula” can refer to a few different date calculations. It might mean extracting the day number from a date, counting the elapsed days between two dates, building a run of dates, or finding a weekday name. Excel has a different approach for each task, so the right formula depends on what you want the result to represent.

A calendar day is any date on the calendar. That differs from a business or working day, which usually excludes weekends and may also exclude specified holidays. This distinction matters for deadlines, project schedules, attendance records, and date-based reports: a 10-calendar-day period is not the same length as 10 working days.

Excel stores valid dates as serial numbers and displays them using a date format. That means date arithmetic works on the underlying values: adding 1 advances a date by one calendar day, while subtracting one date from another returns the number of days between them. The display format changes how a value looks, not the value used in the calculation.

Return the day number, weekday, or full date

If a date is in A2 and you want its numerical day of the month, enter the following in another cell. With January 15, 2025 in A2, the result is 15:

With the date January 15, 2025 in A2, =DAY(A2) returns 15.

That result is just a number; it is not a date and does not include the month or year. For instance, a report showing “15” could refer to the 15th of any month unless the surrounding columns supply the context. Keep the original date in another column if you need to preserve that context.

If you want to advance a date by one calendar day, adding 1 is enough. With January 15, 2025 in A2, =A2+1 returns the date January 16, 2025, provided the result cell is formatted as a date. To move ahead seven calendar days, add 7 instead. Adding days this way does not skip weekends.

To display a weekday as a number, use WEEKDAY. With January 15, 2025 in A2, =WEEKDAY(A2,2) returns 3, because the second argument sets Monday to 1 and Sunday to 7. This is useful for sorting or rules that depend on the weekday. To show the weekday as text, with the same date in A2, =TEXT(A2,"dddd") returns Wednesday. TEXT returns text, so use the original date rather than that text result for date calculations.

Count calendar days between two dates

For elapsed calendar days, put the start date in A2 and the end date in B2. With January 1, 2025 in A2 and January 5, 2025 in B2, =B2-A2 returns 4. The four days are the intervals from January 1 to 2, 2 to 3, 3 to 4, and 4 to 5. This is the usual interpretation of elapsed time between dates.

Sometimes a task asks for the number of dates included in a range, counting both the start and end dates. In that case, use an inclusive count. With A2 set to January 1, 2025 and B2 set to January 5, 2025, =B2-A2+1 returns 5: January 1, 2, 3, 4, and 5. This difference between elapsed days and inclusive days is a frequent source of off-by-one errors.

Choose the convention that matches the question. “How many days after January 1 is January 5?” calls for 4. “How many calendar dates are covered from January 1 through January 5, inclusive?” calls for 5. For a same-day range, subtraction returns 0 elapsed days, while an inclusive count returns 1 date.

If dates might be entered in reverse order, a simple subtraction can produce a negative result. You can either check that the start date is earlier than the end date or use an absolute difference when direction does not matter. With January 5, 2025 in A2 and January 1, 2025 in B2, =ABS(B2-A2) returns 4. ABS removes the sign, so it is not appropriate when a negative result should indicate that the dates are reversed.

Build a sequence of calendar dates

For a short list, enter the first date in a cell and add 1 in the next row. For example, put January 1, 2025 in A2; with A2 holding that date, enter =A2+1 in A3 to get January 2, 2025. Copy the formula down to continue the sequence. If the cells display numbers instead of dates, apply a date format to the result cells.

In Excel for Microsoft 365, you can also generate a run of dates with SEQUENCE. With January 1, 2025 in A2, =SEQUENCE(7,1,A2,1) spills seven consecutive date values into the cells below, covering January 1 through January 7, 2025. Format the spilled cells as dates if they appear as serial numbers. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; in older versions, fill a formula down instead.

When extending a sequence manually, use the fill handle or copy and paste the formula. Excel adjusts the relative reference from A2 to A3, then A4, and so on. If you need each result to refer to one fixed starting date, use an absolute reference such as $A$2 in the formula; that keeps the reference from shifting when copied.

Calendar days versus business days

A formula such as =B2-A2 counts Saturdays, Sundays, and holidays along with weekdays. If you specifically need working days, Excel provides NETWORKDAYS. With January 1, 2025 in A2 and January 7, 2025 in B2, =NETWORKDAYS(A2,B2) returns 5, counting Wednesday through Tuesday but excluding Saturday and Sunday. The function includes both endpoint dates when they are working days.

NETWORKDAYS can also exclude a list of holiday dates. For example, if A2 and B2 contain the start and end dates and H2:H10 contains holiday dates, =NETWORKDAYS(A2,B2,H2:H10) counts weekdays in the range but removes any dates in that holiday list. Make sure the holiday cells contain actual Excel dates, not text that merely looks like a date. If a deadline depends on a contract, policy, or local holiday calendar, confirm which days should count rather than assuming Excel’s weekday convention is the applicable rule.

For the number of calendar dates in a period, do not use NETWORKDAYS: it deliberately excludes weekends and optional holidays. Conversely, if the question is about working days, ordinary date subtraction will overcount. Decide whether the task is measuring calendar time or working time before choosing the function.

Format dates and check the underlying values

To format a date in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Number and select a date format from the number-format list. You can also open Format Cells with Ctrl+1 on Windows and choose a date format. On Mac, use Command+1 to open Format Cells. Formatting a calculated result as a date is particularly important for formulas that return a date serial, such as adding 1 to a date.

A cell formatted as General may show a serial number instead of a familiar date. That does not necessarily mean the formula failed. Conversely, a date-looking display does not prove that Excel recognizes the cell as a date: imported values may be stored as text. If subtraction gives an error or unexpected result, check that both inputs are valid date values and that the workbook has interpreted them in the intended month-day order or day-month order.

Regional settings can affect how typed dates are interpreted. A value such as 03/04/2025 may mean March 4 in one date convention and April 3 in another. To reduce ambiguity when entering dates, use an unambiguous date entry method for your system, check the displayed date after entry, or build dates from separate year, month, and day fields. Cell formatting controls display; it does not repair a date that was originally interpreted incorrectly.

Common formula problems and fixes

  • The result is one day off: Check whether the task wants elapsed days or an inclusive count. Add 1 only when both endpoint dates should be counted.
  • The formula returns a number: Date subtraction is supposed to return a day count. A formula that returns a date, such as adding 1 to a date, may need a date format instead.
  • The result is negative: Verify which date is in the start cell and which is in the end cell. Use ABS only if the direction genuinely does not matter.
  • The formula shows an error: Look for a date stored as text, an empty input, or a mistyped cell reference. Convert or re-enter the input as a real date before calculating.
  • The day number is correct but lacks context: DAY returns only a number from 1 to 31. Keep the full date or show the month and year in adjacent cells when readers need to identify the date.

For formulas copied across a table, also check whether cell references should move. Relative references such as A2 adjust when copied, while absolute references such as $A$2 remain fixed. A misplaced dollar sign can make a sequence repeat the same date or calculate from the wrong input.

Frequently Asked Questions

What formula returns the day of the month in Excel?
Use =DAY(A2) when A2 contains a valid Excel date. It returns a number from 1 to 31, not the full date or the weekday name.

How do I count calendar days including both dates?
With the earlier date in A2 and the later date in B2, use =B2-A2+1. The +1 includes both endpoints; without it, subtraction returns elapsed days between them.

Does Excel count weekends when I subtract dates?
Yes. Subtracting one valid date from another counts every calendar day in the elapsed interval, including weekends and holidays. Use NETWORKDAYS when you need to exclude weekends, and provide holiday dates if those should also be excluded.

How can I add one calendar day to a date?
With the starting date in A2, use =A2+1. The result is the next calendar date, even if that date falls on a weekend. Format the result cell as a date if Excel displays a serial number.

Why does Excel show a number instead of a date?
Excel stores dates as serial values, and a cell using General format may show that underlying number. Apply a date format to display it as a calendar date. If the input is text rather than a real date, formatting alone may not fix the calculation; check and correct the input value.

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.