1.8 DATEDIF Function

The DATEDIF function in Excel calculates the difference between two dates. You can tell Excel to return to you the difference between dates in days, months, or years. In many versions of Excel, the DATEDIF function does not show up in the list of functions, but the command still works when entered in the manner shown below. The formula requires three parameters to be entered: (1) the start date, (2) the end date, and (3) the unit of time to be used when computing the difference (e.g., day, month or year).

The image below shows an example of how this command could be used. Notice the tree parameters in the formula in cell C5. The first tells Excel what the start date is (in this problem "Beginning Date"). The second parameter tells Excel which cell contains the end date. The third parameter tells Excel whether you would like it to return to you the difference between these dates in the number of months, years or days. The "M" tells Excel to return the number of months. If you wanted the number of years difference between the two, you would use "Y" and you would use "D" if you want Excel to return the number of days difference.

You should note that if you use the number of years difference in this problem, the answer will be "0." This is because the start date and end date are both in 2023. While you might excpect Excel to return a fraction, such as 3/12 of a year since there are 3 months between June 30 and September 30, Excel only looks at the year and recognizes there is no difference between 2023 in the beginning date and 2023 in the ending date. For months, the formula will return the number of months difference between two dates, but it does factor in differences in days. When the day in each date is different, Excel always rounds the solution down. For example, in the example below, Excel would return an answer of 3 because there are three months difference between June 30th and September 30th. However, if the beginning date were June 1, 2023 you might expect Excel to return an answer of 4. However, Excel still notices that there are fewer than 4 months difference (by 2 days), so it rounds down to 3 months. Similarly, if you changed the beginning date to May 31, 2023 Excel would not recognize that there are 4 months difference, because it is still one day short of the 30th, and would still return a 3 month difference. Similar issues arise if you change the end date to anything prior to the 30th of September. Excel would recognize that there are not 3 full months anymore and would change the result to 2 months difference, even if you only changed it by one day. Thus, you need to be careful when using this formula, and make any appropriate manual adjustments to the solution to correct this in the manner you prefer.