Excel date math works by treating dates as numbers: subtract one date from another to count elapsed days, add a number to move forward by days, and use calendar functions for workdays or month boundaries. For example, if A2 contains a start date and B2 an end date, =B2-A2 returns the number of days between them.
How Excel represents dates
A date that looks like March 1, 2025, is normally stored as a serial number, with the whole-number portion counting days from a starting point. A time is stored as a fraction of a day: noon is one-half, and 6 a.m. is one-quarter. This is why ordinary addition and subtraction can calculate date and time intervals.
Excel’s standard 1900 date system assigns serial numbers to dates beginning in 1900. It preserves a historical compatibility error by treating 1900 as a leap year, even though it was not. The result is a fictitious February 29, 1900, in the serial sequence. This unusual case rarely affects current dates, but it matters if a workbook processes very old dates or compares serial values across programs.
Some older workbooks use the 1904 date system, in which serial zero represents January 1, 1904. The systems differ by 1,462 days. Changing a workbook’s date system can therefore shift displayed dates, so do not switch it casually in a file containing dates. In Excel for Microsoft 365 on Windows, the setting is under File > Options > Advanced > When calculating this workbook. Check the setting before comparing serial values from different workbooks.
Cell formatting controls how a stored value appears, not the underlying date calculation. A serial number may display as a date when the cell has a date format, or as a number if it has a General or Number format. This distinction explains why a formula can calculate correctly while its result looks unexpected.
Subtract dates and count days
For a basic interval, put the earlier date in A2 and the later date in B2. If A2 is February 1, 2025, and B2 is March 1, 2025, the subtraction returns 28. The result counts the midnight-to-midnight difference; it does not count both endpoint dates as separate days.
With the start date in A2 and the end date in B2, enter =B2-A2; for February 1 through March 1, 2025, the result is 28 days.
Sometimes a report needs an inclusive count, such as the number of calendar dates covered by a reservation. With the same dates in A2 and B2, enter =B2-A2+1; the result is 29 dates, counting February 1 and March 1. Whether to add one depends on the question: elapsed duration and inclusive date count are not the same measure.
Adding a whole number moves a date forward by that many calendar days. With the starting date in A2 as February 1, 2025, enter =A2+30; the result is March 3, 2025. Use a negative number to move backward. If the number of days is in C2 instead, adding C2 makes the offset easy to change without editing the formula.
To construct a date from separate year, month, and day values, use DATE rather than joining text. With the year in C2, month in B2, and day in A2, enter =DATE(C2,B2,A2). For C2 equal to 2025, B2 equal to 3, and A2 equal to 1, Excel returns March 1, 2025. DATE also normalizes out-of-range month or day values, which can be useful but may conceal bad source data if you do not validate inputs.
Move by months and find month ends
A month is not a fixed number of days, so adding 30 is not a reliable way to move one calendar month. Use EDATE when you need a date the requested number of months before or after another date. It preserves the day number when possible and adjusts to the last day of the destination month when that day does not exist.
With January 31, 2025, in A2, enter =EDATE(A2,1); the result is February 28, 2025. With a negative month offset, EDATE moves backward. This is useful for recurring dates such as monthly reviews, but it does not mean every resulting interval has the same number of days.
To find the last day of a month, use EOMONTH. With any date in A2, enter =EOMONTH(A2,1) to return the final date of the following month. If A2 is January 31, 2025, the result is February 28, 2025. An offset of zero returns the end of the month containing A2. These functions are more dependable for calendar schedules than approximating a month as 30 days.
EDATE and EOMONTH return date serial values. Apply a date format if the result appears as a number. Both are available in current Excel versions; in older installations, Excel may prompt users to enable the Analysis ToolPak when first using these functions.
Calculate workdays and holidays
Calendar-day arithmetic includes weekends and holidays. For a work schedule, NETWORKDAYS counts Monday through Friday between two dates, including both endpoints when they are workdays. You can provide a holiday range to exclude additional dates.
With the start date in A2, end date in B2, and holiday dates in D2:D3, enter =NETWORKDAYS(A2,B2,$D$2:$D$3). If A2 is Monday, January 6, 2025, B2 is Friday, January 10, and D2 contains January 8 and another holiday, the result is 4 working days: Monday, Tuesday, Thursday, and Friday. The dollar signs keep the holiday range fixed if you copy the formula down.
WORKDAY calculates a date a specified number of working days away, omitting weekends and optional holidays. With January 6, 2025, in A2, and a holiday on January 8 in D2, enter =WORKDAY(A2,5,$D$2); the result is January 14, 2025. The starting date is not counted as day one: Tuesday the 7th is the first workday, then Thursday the 9th, Friday the 10th, Monday the 13th, and Tuesday the 14th.
For work schedules with different weekend days, use NETWORKDAYS.INTL or WORKDAY.INTL. These functions let you specify a weekend pattern, such as Friday and Saturday, rather than assuming the standard Saturday-and-Sunday weekend. Check the function’s weekend argument carefully, and keep holiday cells as real Excel dates rather than text that merely looks like dates.
Work with hours and elapsed time
Because time is a fraction of a day, subtracting date-and-time values gives a duration measured in days. With a start timestamp of January 1, 2025, at 9:00 a.m. in A2 and an end timestamp of January 2 at 5:30 p.m. in B2, enter =B2-A2. The underlying result is 1.3541667 days, equal to 32 hours and 30 minutes.
To display that duration as hours and minutes, format the result cell with the custom number format [h]:mm. Square brackets allow the hour count to exceed 24; a standard time format may wrap after a full day and show 8:30 instead of 32:30. In Excel for Microsoft 365 on Windows, select the cell and press Ctrl+1 to open Format Cells, choose Custom, and enter the format. On Mac, Command+1 opens Format Cells.
To return a numeric count of hours instead, multiply the difference by 24. With the same start and end timestamps in A2 and B2, enter =(B2-A2)*24; the result is 32.5 hours. For minutes, multiply the date-time difference by 1,440, the number of minutes in a day. Decide whether the result should be a numeric quantity or a displayed duration, then format it accordingly.
Display dates and avoid text-date problems
If a result displays as a serial number, select the cell and choose a date format from Home > Number Format. A short date format is suitable for most date-only results; a custom format can show weekday names or times. Formatting changes presentation, not the value used by formulas.
Text that resembles a date is not always a usable date value. Imported data such as “03/04/2025” can be ambiguous: it may mean March 4 or April 3, depending on regional conventions. A formula may fail or return an unexpected result if the source is text. Check whether dates behave as numbers, and use a consistent import convention. When assembling a date from known numeric parts, DATE avoids many regional parsing ambiguities.
Typing a date directly into a formula can also depend on regional settings. For dependable formulas, use cell references or build the date with DATE and numeric year, month, and day arguments. Do not assume that every computer interprets a slash-separated date in the same way.
Other common causes of confusing results include:
- End date earlier than start date: subtraction returns a negative number. Check the data or decide whether the formula should use absolute duration.
- Unexpected weekend count: NETWORKDAYS uses a standard weekend unless you choose an .INTL function.
- Off-by-one totals: decide whether you need elapsed days or an inclusive count of calendar dates.
- Dates shifted between workbooks: check whether the workbooks use different date systems before comparing serial values.
- Formula result shows ####: widen the column or review the number format; the hashes do not necessarily mean the calculation failed.
Choose the right date function
Match the calculation to the meaning of the question, rather than choosing a function because its name sounds close. These distinctions cover many everyday cases:
- Count elapsed calendar days: subtract the earlier date from the later date.
- Count dates including both endpoints: subtract and add one, if that matches the reporting rule.
- Move a fixed number of days: add or subtract a whole number.
- Move a number of calendar months: use EDATE; for the end of a month, use EOMONTH.
- Count working days or calculate a workday deadline: use NETWORKDAYS or WORKDAY, with holidays where appropriate.
- Calculate a duration that may exceed a day: subtract date-time values and format the result as
[h]:mm.
A useful check is to test a formula against a small, known example before filling it down a large table. Confirm the dates, whether the endpoints count, the weekend definition, and the displayed format. Those choices often matter more than the arithmetic itself.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
Subtract the earlier date from the later date. If the earlier date is in A2 and the later date is in B2, use =B2-A2. Add one only when you want to count both endpoint dates inclusively.
Why is Excel showing a number instead of a date?
Excel stores dates as serial numbers, and the cell may be formatted as General or Number. Apply a date format to display it as a calendar date; the serial value itself is not necessarily an error.
How do I add one month to a date?
Use EDATE rather than adding 30 days. With the date in A2, =EDATE(A2,1) returns the date one calendar month later, adjusting to the last valid day when needed.
How do I calculate working days while excluding holidays?
Use NETWORKDAYS for the count between two dates or WORKDAY to find a date a number of workdays away. Supply a range containing actual holiday dates, and use the .INTL versions if your weekend differs from Saturday and Sunday.
Why does a time difference show the wrong number of hours?
Excel stores elapsed time as a fraction of a day, and ordinary clock formats can wrap after 24 hours. Use the custom format [h]:mm for a duration, or multiply the date-time difference by 24 to return hours as a number.
Related
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR with VLOOKUP in Excel: Hide Lookup ErrorsYes. With lookup IDs in A2:A100, return values in C2:C100, and the value to search for in E2,…
- How to Compare Two Columns in Excel: Formulas and TipsTo compare values on the same row, put the lists in columns A and B, then enter a…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- Excel Formula for Percentage of a NumberTo calculate a percentage of a number in Excel, multiply the number by the percentage: if A2 contains…
- Excel WORKDAY Formula: Does It Exclude Weekends?Yes. In Microsoft Excel for Microsoft 365 on Windows, WORKDAY excludes Saturday and Sunday by default when it…
- Excel Business Days: NETWORKDAYS and WORKDAY FormulasTo count weekdays between two dates in Excel, use =NETWORKDAYS(B2,B3); for example, with a Monday start date in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.