Table of Contents
In this article, we’ll talk about how to Calculate Difference Between Two Dates in Excel You’ll learn how to count the number of days, weeks, months, and years between two dates in several ways. In the last few weeks, we’ve looked into almost every part of using dates and timings in Excel.
So, Excel has a couple functions that tell you how many days separate two different dates. You can add one of four Excel functions to a cell to find out how many days are between two dates. If you’ve been reading our blog series, you already know how to add and format dates in your worksheets, calculate weekdays, weeks, months, and years, and add and subtract dates.
We might need to know how many months have elapsed between two dates if we work on a lot of projects. In the same way, if we are planning a project, we may need to know when it will start and end. There are different techniques to figure out how many months there between two dates.
Ways to Calculate Difference Between Two Dates in Excel
Without a Function
First, you can subtract two dates to discover the difference between them. Excel doesn’t have a “subtract” button, but you may still put formulas in your cells that accomplish subtraction. Here are some tips for dating. You can also go to the official Microsoft Excel support site to know more information.
- Open a “blank” Excel spreadsheet, and enter a “start” and “end” date in cells “B4” and “C4” as in the snapshot below. Note that the dates should be in U.S. format with the month first, the day second, and the year third.
- Now, select cell “D4,” click inside the “formula bar” at the top, then input “
=C4-B4” and press “Enter.” Cell “D4” will return a value of “34.” Note: “C4” comes first because you are subtracting.
Using DATE Function
With the DATE function, you may find the difference between two dates. Then, you may find the number of days by putting the dates in the function bar instead of the spreadsheet cells. “=DATE(yyyy, m, d)-DATE(yyyy, m, d)” is the fundamental way to write that function. To figure out the difference correctly, the most recent date goes in first.
- Select a “cell” on the spreadsheet where you want to add the function, then ensure it is set to “General” format.
- Click inside the “function bar,” then type “=DATE(2022, 5, 5)-DATE(2022, 4, 1)” and press “Enter.”
Using DATEDIF Function
You can use the DATEDIF function to figure out how many days have passed by putting dates on the spreadsheet or in the function bar. But DATEDIF isn’t in the list of functions in Excel’s Insert Function window because it only works with Lotus 1-2-3 workbooks.
If you want to utilize the “DATEIF” function, you’ll have to type it right into the function bar. The following are part of the syntax: DATEDIF(start_date, end_date, unit). You can add the unit “days” to the function after entering a start date, an end date, or cell references to individual dates. Here’s what you need to do.
- Select the “cell” on the spreadsheet where you will place the function, then set it to “General” format.
- To find the difference in days (including years) entered in cells B6 and C6, type “=DATEDIF(B6, C6, “d”)” into the function bar and press “Enter.” The “d” represents the format, “days.”
- If you need to ignore years in the calculation, replace “d” with “yd” so you get a formula listed as “=DATEDIF(B4, C4, “yd”).” The “y” excludes years, but the “d” includes “days.”
Using DAYS360 Function
The DAYS360 function uses a 360-day calendar to find the total number of days between two dates. This type of calendar is often used for financial years. So, this might be a better way for account spreadsheets to work. When the dates are only a few months apart, it won’t make much of a difference, but when the dates are farther apart, DAYS360 will return somewhat different values than the other functions.
- Enter ‘1/1/2021′ and ‘1/1/2022‘ in cells B6 and C6 on your spreadsheet.
- Then click a cell to include the DAYS360 function in, press Formulas > Date & Time.
- From the Date & Time dropdown menu, select DAYS360.
- Press the Start_date button and type in “B6,” click the End_date button and type in “C6,” and then press OK.
- The DAYS360 function will return a value of 360.