OmniDocExcel Date Formulas: Calculate Days and Months
Learn the Excel date formulas for counting days, months and working days between dates, with steps to set up cells correctly and checks to confirm your results.
Date formulas for spreadsheets are one of a common things people look up, and they cause more confusion than they should. You have a start date and an end date, and you need the number of days, the number of full months, or a deadline 90 days out. The formulas for this are short, but a single wrong cell format or a date typed as text can quietly produce a wrong answer.
This guide covers the Excel formulas for dates that handle the work most people need: counting days, counting complete months and years, adding months to a date, and counting working days. It focuses on date arithmetic and the date functions themselves, not general number formulas. You will also learn the setup steps that prevent errors and how to check the result.
The short answer is that simple subtraction counts days, DATEDIF counts complete months or years, EDATE adds months, and NETWORKDAYS counts working days.
Which date formula should you use? The short answer
Pick the formula based on the question you are answering. Most date problems fall into one of the five cases below.
| Your situation | What to do | Why |
|---|---|---|
| You need the total number of days between two dates | Subtract the start date from the end date | Dates are stored as day counts, so subtraction returns days directly |
| You need complete months or years, like a contract term or age | Use DATEDIF with the unit "m" or "y" | It counts only full units and ignores leftover days |
| You need a date a set number of months later, like a renewal | Use EDATE with the start date and the number of months | It keeps the same day of the month when possible |
| You need the last day of a month, like a billing cycle end | Use EOMONTH with the start date and a month offset | It returns the final calendar day of the target month |
| You need working days, excluding weekends and holidays | Use NETWORKDAYS to count, or WORKDAY to find a date | It skips Saturdays and Sundays and any holiday list you give it |
Before you start
Check these items first. Each one is a common reason a date formula returns an error or a number that looks wrong.
- Confirm the cells contain real dates. Dates that are stored as text look correct but do not calculate. Right-aligned values usually indicate real dates; left-aligned values often mean text.
- Check the date order in your data. A date like 03/04/2024 means March 4 in some regions and April 3 in others. Entering dates with DATE(year, month, day) removes this ambiguity.
- Make sure the start date comes before the end date. DATEDIF returns an error when the start date is later than the end date. Subtraction returns a negative number instead.
- Apply a date format to the result cell. A formula that returns a date can display as a large number if the cell is still formatted as a general number. Format the cell as a date, or as a plain number when you want days.
- Note whether the file was translated. If your spreadsheet came from OmniDoc's translation of an .xlsx file, the text labels are translated, but you should still confirm that formulas and date cells return the values you expect.
Calculating days and months between dates, step by step
Use the steps below with two sample dates: a start date of March 15, 2024, in cell A2, and an end date of July 2, 2024, in cell B2. Those dates give 109 days, 3 complete months and 17 leftover days.
- Enter the dates as real date values. Type each date in its own cell, or use =DATE(2024,3,15) and =DATE(2024,7,2) so the year, month and day are explicit.
- Count total days. In a result cell, enter =B2-A2. The result is 109, because a date is stored as a count of days, so subtraction gives the gap directly.
- Count complete months. Enter =DATEDIF(A2,B2,"m"). This returns 3, because only three full months fit between March 15 and July 2.
- Count leftover days after the full months. Enter =DATEDIF(A2,B2,"md"). This returns 17, the days left over after the three complete months are removed.
- Count complete years. For a span longer than a year, enter =DATEDIF(A2,B2,"y"). Use this for ages, tenure or contract length.
- Add months to a date. To find a renewal date six months after a start date, enter =EDATE(A2,6). The result is September 15, 2024.
- Find the end of a month. Enter =EOMONTH(A2,0) for the last day of the start date's month, or =EOMONTH(A2,3) for the last day of the month three months later.
- Count working days. Enter =NETWORKDAYS(A2,B2) to count weekdays between the two dates, including both ends. To skip holidays, list them in a range and add it as the third argument.
Check the result
Test each formula before you copy it down a column or use it in another calculation. The table lists the checks that catch most date errors.
| Check | How to see it | If it's wrong |
|---|---|---|
| The cells hold real dates | Select the cell and look at the alignment and the formula bar | Re-enter the date, or use DATE(year, month, day), so the cell stores a date value |
| The day count matches a manual count | Count the days on a calendar for a short span, or add days to the start date until you reach the end date | Check the cell order in the formula and confirm the start date is the earlier one |
| DATEDIF units are correct | Compare the result with a full month count on a calendar | Use "m" for complete months, "md" for leftover days, and "y" for complete years |
| The result displays as a date or number | Look at the cell display and the underlying value | Change the cell format to a date or to a number, depending on what you need |
| Working days exclude the right dates | Scan a short range for weekends and listed holidays | Confirm the holiday list contains real dates and the range you passed is correct |
❓ FAQ
1. Why does my date formula return a number like 45000?
The cell is storing a date as a serial number, which is how spreadsheets track dates internally. Format the cell as a date to see the calendar value. If you need the number of days, keep it as a plain number.
2. What is the difference between DATEDIF and simple subtraction?
Subtraction returns the total number of days between two dates. DATEDIF returns whole units, such as complete months or years, and leaves the remainder out. Use subtraction for totals and DATEDIF for durations described in full months or years.
3. How do I count days that include the end date?
Subtraction counts the gap between dates, so add 1 to include the end date. For example, =B2-A2+1 counts both March 15 and July 2 when they are both part of the period.
4. Why does my formula show #VALUE! for dates that look correct?
One of the cells is probably text. Check the alignment, then re-enter the date or use the DATE function to create a true date value.
📝 Summary
Most date questions come down to one of a few formulas: subtraction for total days, DATEDIF for complete months and years, EDATE for adding months, and NETWORKDAYS for working days. Set up your cells as real dates, enter the start and end dates in the right order, and check one result by hand before copying a formula down a column.
