3 methods for finding the number of days between dates in Excel
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 (-).
Steps
Section 1 of 4:
Using the DATEDIF Function
-
Type the start date in a cell. 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] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- If you’re searching for a specific date in a large dataset, try using the VLOOKUP function .
-
Type the end date in a second cell. For example, 1/17/2023 in cell A2.Advertisement
-
Type =DATEDIF(A1, A2, "d") in a third cell and press ↵ Enter . 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] X Research source
- 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.
Advertisement
Section 2 of 4:
Using the DAYS Function
-
Type the start date in a cell. For example, you could put 1/10/2023 in cell A1. The DAYS function calculates the number of days between dates. [3] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
- 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.
-
Type the end date in a second cell. For example, 1/17/2023 in cell A2.
-
Type =DAYS(A2, A1) in a third cell and press ↵ Enter . 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] X Research source
- 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.
Advertisement
Section 3 of 4:
Using Subtraction
-
Type the start date in a cell. For example, you could put 1/10/2023 in cell A1. You can use the subtraction operation (-) to find the days between two dates.
-
Type the end date in a second cell. For example, 1/17/2023 in cell A2.
-
Type =A2-A1 in a third cell and press ↵ Enter . 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.
Advertisement
Section 4 of 4:
Changing Date Formats
-
Select your entire sheet. 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.
-
Right-click the cells and choose Format Cells… . It's near the bottom of the drop-down list.
-
Click the Number tab, then select the Date category. Date will be in the vertical box on the left side of the pop-up window, under a header that says "Category."
-
Click the dropdown box under "Locale (location)." 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)."
- 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.
-
Choose your preferred date format under the "Type" heading. 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.
-
Click OK to save your selection. 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.
- To change Excel's default date format, you'll have to change your computer's default region.
Advertisement
Expert Q&A
Search
-
QuestionWhat 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 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.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.
-
Questionhow to calculate no.of.days between two dates without mentioning the date in any cells/columnsKyle 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.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.
-
QuestionHow many days between 22 June 2019 and 10 Jan 2020?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.Using the formula =DAYS("1/10/2020","6/22/2019"), there are 202 days between 22 June 2019 and 10 Jan 2020.
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
Thanks for submitting a tip for review!
References
- ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
- ↑ https://www.geeksforgeeks.org/datedif-function-in-excel/
- ↑ https://support.microsoft.com/en-us/office/days-function-57740535-d549-4395-8728-0f07bff0b9df
- ↑ 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.
Advertisement