The simplest way to find days between dates

To find how many days lie between two dates in Excel, subtract the earlier date from the later date. If your start date is in cell A1 and your end date is in cell B1, type =B1-A1 into an empty cell. Excel will show you the number of days between them.

This works because Excel stores dates as numbers counting from January 1, 1900. When you subtract one date from another, you get the count of days in between. The result appears as a whole number — no decimal places, no extra formatting needed.

If your result shows as a date instead of a number (like "1/0/1900" or something equally wrong), right-click the cell, choose Format Cells, select Number from the Category list on the left, and click OK. Now the cell will display the day count correctly.

Key Takeaways

  • Subtract the earlier date from the later date using a straightforward formula like =B1-A1 to get the number of days between them.
  • If Excel shows your result as a date instead of a number, format the cell as Number rather than Date.
  • The DATEDIF function calculates days, months, or years between dates and is useful when you need units other than days.
  • When dates are entered as text instead of actual dates, Excel cannot do math with them — you may need to convert them first.
  • Negative results mean you subtracted the later date from the earlier one; reverse the order to fix it.

Calculating months or years instead of days

If you need the difference in months or years rather than days, use the DATEDIF function. Type =DATEDIF(A1,B1,"M") to get months, or =DATEDIF(A1,B1,"Y") to get complete years. The letter in quotes tells Excel which unit you want: "D" for days, "M" for months, "Y" for years.

DATEDIF is stricter than straightforward subtraction — it counts only complete units. If your dates are 15 months apart, DATEDIF with "M" returns 15, not 1. If they are 1 year and 3 months apart, DATEDIF with "Y" returns 1, not 1.25.

One quirk: DATEDIF sometimes refuses to work if your dates are in the wrong order or if one cell is empty. If you get an error, double-check that your start date really comes before your end date, and that both cells contain actual dates, not text that looks like dates.

When dates are stored as text and won't calculate

Sometimes dates appear in cells but Excel treats them as text — the subtraction formula returns an error, or DATEDIF refuses to work. This happens when dates were typed in as text, imported from another program, or pasted from a website.

To check: click the cell with the date. If it is text, Excel shows a small green triangle in the corner, or the cell is left-aligned instead of right-aligned (dates are normally right-aligned by default). You can also type a formula like =ISTEXT(A1) in another cell; if it returns TRUE, the date is text.

To convert text dates to real dates, select the column, go to the Data menu, and choose Text to Columns. Click Next twice, make sure the column format is set to Date, and click Finish. Excel will convert the text to actual dates you can do math with. If this does not work, your dates may be in a format Excel does not recognize — try retyping a few in the format MM/DD/YYYY or DD/MM/YYYY depending on your region.

Handling time of day in your calculation

When your cells contain both a date and a time (like "3/15/2024 2:30 PM"), subtraction still works, but the result includes a decimal. The whole number part is days; the decimal part represents the hours and minutes. A result of 5.5 means 5 days and 12 hours.

If you want only the whole days and do not care about partial days, wrap your formula in the INT function: =INT(B1-A1). This rounds down to the nearest whole number, dropping any time-of-day information.

If you need hours or minutes as a separate count, you can extract them from the decimal. Multiply the decimal part by 24 to get hours: =(B1-A1-INT(B1-A1))*24. This is rarely necessary for basic date math, but it is useful if you are tracking elapsed time in a project or shift log.

Negative results and reversed dates

If your formula returns a negative number, you subtracted the later date from the earlier one. A result of -10 means you need to flip the order of your dates. Change =A1-B1 to =B1-A1 and the result will be positive.

Negative results are not wrong — they straightforward tell you the direction. Some spreadsheets intentionally use them to show whether a important date was missed (negative days remaining) or how far ahead of schedule something is (positive days). But for a straightforward "how many days between" question, a positive number is clearer.

Common mistakes and how to fix them

The most frequent problem is forgetting that Excel needs actual dates, not text. If your formula returns #VALUE! error, the cells probably contain text. Use the Text to Columns method described above to convert them.

Another common issue: you get a result like 44927 instead of a reasonable number of days. This usually means one of your cells contains a number that looks like a date but is not formatted as one. Click the cell, go to Format Cells, choose Date from the Category list, pick a date format, and click OK. Then try your formula again.

If DATEDIF returns #NUM! error, check that your start date comes before your end date. DATEDIF cannot work backwards. Also make sure neither cell is empty — if one is, the function fails.

Frequently Asked Questions

Can I calculate the difference between dates in different time zones?

Excel does not have built-in time zone awareness. If your dates include time and come from different zones, you need to convert them to the same zone first — usually UTC or your local time. Once they are in the same zone, the subtraction formula works normally. This is rarely needed for straightforward date math unless you are working with international project timelines.

What if I want to exclude weekends from my day count?

Use the NETWORKDAYS function instead of subtraction. Type =NETWORKDAYS(A1,B1) to count only weekdays between two dates. You can also add a third argument to exclude specific holidays: =NETWORKDAYS(A1,B1,C1:C10) where C1:C10 contains a list of holiday dates.

How do I calculate someone's age in years from their birth date?

Use =DATEDIF(A1,TODAY(),"Y") where A1 contains the birth date. TODAY() is a function that returns today's date automatically, so the formula always gives the current age. If you want age in years and months, use =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months".

Why does my formula show the result as a date instead of a number?

Excel is explore Date formatting to a cell that should show a number. Right-click the cell, select Format Cells, choose Number from the Category list on the left side, and click OK. The cell will now display the day count as a number instead of trying to interpret it as a date.

Can I calculate the difference between dates in different spreadsheets?

Yes. Reference the other file in your formula by typing the file name in square brackets, followed by the sheet name and cell: =[OtherFile.xlsx]Sheet1!B1-[OtherFile.xlsx]Sheet1!A1. Both files must be open for this to work. If you close the other file, Excel converts the reference to its full path.