Easily Calculate the Number of Days Between Two Dates in Microsoft Excel

PDF download Download Article
3 methods for finding the number of days between dates in Excel
PDF download Download Article

Trying to find how many days there are between two dates in Excel? There are a few easy methods for doing so! You can use the DATEDIF or DAYS functions. Or, just subtract the two dates! This wikiHow guide will show you how to calculate the number of days between two dates in Microsoft Excel.

Calculating Days Between Dates in Excel

Use =DATEDIF(start_date, end_date, "d") to find the difference in days between two dates. Or, Use =DAYS(end_date, start_date) to calculate the days between two dates. Alternatively, subtract the two dates using the subtraction operator (-).

Section 1 of 4:

Using the DATEDIF Function

PDF download Download Article
  1. For example, you could put 1/10/2023 in cell A1. The DATEDIF function calculates the difference between two dates in days, months, or years. [1]
    • If you’re searching for a specific date in a large dataset, try using the VLOOKUP function .
  2. For example, 1/17/2023 in cell A2.
    Advertisement
  3. For example, this formula could be in A3. Replace A1 and A2 with the cells containing the start and end dates. This will find the number of days between cells A1 and A2. You’ll see the result in the cell you selected for the function. [2]
    • The first parameter (A1) is the start date, and the second parameter (A2) is the end date. The start date must be before the end date, or the function will return a #VALUE error.
    • Replace "d" with "m" to find the difference in months or "y" to find the difference in years.
    • Copy this formula as needed and paste it next to other dates you’re trying to find the difference between.
    4. Advertisement
Section 2 of 4:

Using the DAYS Function

PDF download Download Article
  1. For example, you could put 1/10/2023 in cell A1. The DAYS function calculates the number of days between dates. [3]
    • This function is useful for plenty of applications! For example, if you’re tracking your bills in Excel , you can calculate the days between two dates to average your per-day spending.
  2. For example, 1/17/2023 in cell A2.
  3. For example, this formula could be in A3. Replace A2 and A1 with the cells containing the start and end dates. This will find the number of days between cells A1 and A2. You’ll see the result in the cell you selected for the function. [4]
    • The first parameter (A2) is the end date, and the second parameter (A1) is the end date. This is opposite the order of the DATEDIF function. If you put the start date before the end date, the function will calculate a negative number.
    4. Advertisement
Section 3 of 4:

Using Subtraction

PDF download Download Article
  1. For example, you could put 1/10/2023 in cell A1. You can use the subtraction operation (-) to find the days between two dates.
  2. For example, 1/17/2023 in cell A2.
  3. For example, this formula could be in A3. Replace A2 and A1 with the cells containing the start and end dates. This will find the number of days between cells A1 and A2. You’ll see the result in the cell you selected for the function.
    • The first parameter (A2) is the end date, and the second parameter (A1) is the end date. This is opposite the order of the DATEDIF function. If you put the start date before the end date, the function will calculate a negative number.
    • Using the SUM function is less useful for dates. Since dates are stored as serial numbers in Excel, SUM would just add those numbers together.
    4. Advertisement
Section 4 of 4:

Changing Date Formats

PDF download Download Article
  1. To do this, click the triangle in the upper-right corner of the sheet, between column A and row 1. You can do this before or after you've typed in your dates.
    • Depending on where you live, the standard date format might be Month-Day-Year (MDY), Day-Month-Year (DMY), or Year-Month-Day (YMD). You can change it on a sheet-by-sheet basis.
  2. It's near the bottom of the drop-down list.
  3. Date will be in the vertical box on the left side of the pop-up window, under a header that says "Category."
  4. From here, you can choose a location that formats dates in the way that you prefer. You can choose any location you want; changing the location in the Date section will only affect the date formats available to you. It won't change what language you use in Excel.
    • If the locale is set to a location other than where you're actually located, change it to the language/country that you're in.
      • For example, if you're in Australia, you'd choose "English (Australia)."
    • If you live in a different country but prefer a different date format, choose a country that has the date format you want.
      • If you want the most options, choose "English (Canada)." Canada is one of three countries in the world (alongside Kenya and Ghana) that officially use MDY, DMY, and YMD. As such, all three options will be available if you change your locale to "English (Canada)."
  5. Any of these formats will work when using one of the formulas above to calculate the number of days between two dates. The format you choose will simply tell Excel how to auto-format any date you enter into a cell.
    • If you're ever concerned that writing a date out [[Prevent-Excel-from-Removing-Leading-&-Trailing-Zeros|in numbers will result in the wrong date being input into your spreadsheet, write out the date like "January 1, 2012," where you spell out the month. Excel will then auto-format the date for you.
  6. Now all of the cells in your document will format dates in the way that you prefer. This only applies to your current document, however. If you create a new sheet, you'll need to repeat these steps to change your default date format.
    • To change Excel's default date format, you'll have to change your computer's default region.
      • Windows: Type region in the taskbar search bar, then click Set regional format . Under the "Region" heading, click the dropdown next to "Country or region" and choose what region you live in or what region you're from.
      • Mac: Click the Apple menu and go to System Settings > General > Language & Region . Next to "Region," choose the region you live in or what region you're from.
    7. Advertisement

Expert Q&A

Search
Add New Question
  • Question
    What is the formula to do a count down in days in a cell in Excel? Can I have the font turn red on day 0?
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    You can use =DAYS(A1,TODAY()) to make a countdown in days. Replace "A1" with the cell that contains the date you're counting down to. To make the font turn red on day 0, select the cell with the DAYS formula. Go to the Home tab > Conditional Formatting > New Rule. Click "Format only cells that contain." Change "between" to "equal to" in the drop down menu. Enter 0 to the right of "equal to." Click "Format" and change the Font color to red. Click OK on both windows to confirm the change.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 0 Helpful 0
  • Question
    how to calculate no.of.days between two dates without mentioning the date in any cells/columns
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    You can input the start and end dates as text in the DAYS function to calculate the number of days without needing to reference another cell. For example, =DAYS("4/15/2023","4/1/2023") would return the number of days between 4/15/2023 and 4/1/2023.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 2 Helpful 2
  • Question
    How many days between 22 June 2019 and 10 Jan 2020?
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
    Kyle Smith
    wikiHow Technology Writer
    Expert Answer
    Using the formula =DAYS("1/10/2020","6/22/2019"), there are 202 days between 22 June 2019 and 10 Jan 2020.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 2 Helpful 0
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Make a Spreadsheet in Excel
      The Best Ways to Make & Format Lists in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      How to
      Password Protect a Microsoft Excel Spreadsheet
      How to
      Add Numbers & Cells in Excel: Complete Guide
      How to
      Create an Inventory List in Microsoft Excel: Step-by-Step Guide
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Link Sheets in Excel
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      How to
      Create a Pie Chart in Microsoft Excel: Simple Steps
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
      2. ↑ https://www.geeksforgeeks.org/datedif-function-in-excel/
      3. ↑ https://support.microsoft.com/en-us/office/days-function-57740535-d549-4395-8728-0f07bff0b9df
      4. ↑ https://ecampusontario.pressbooks.pub/businessmath/chapter/calculating-the-number-of-days-between-two-dates-in-ms-excel/

      About This Article

      Thanks to all authors for creating a page that has been read 164,729 times.

      Is this article up to date?

      Advertisement