![]() This formula uses the YEAR function and the MONTH function to work out a count in two parts. The generic formula for this calculation looks like this: =(YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5)+1 In some cases, you may want to count how many months are "touched" by a given date range. Alternative formula to calculate months touched by dates Keep in mind that the behavior of this formula depends on how YEARFRAC works. YEARFRAC returns fractional years between two dates, so the result is an approximate number of months between two dates, including fractional months returned as a decimal value. The screen below shows how the original DATEDIF formula compares to the YEARFRAC version: The screen below shows how the original DATEDIF formula compares to the modified version:Īs shown above, DATEDIF only calculates whole months. To calculate a decimal number that represents the number of months between two dates, you can use the YEARFRAC function like this: =YEARFRAC(start,end)*12 End dates that occur in the first half of the month also increase by 15 days, but not enough to affect the normal result from DATEDIF. This ensures that end dates in the second half of the month are treated like dates in the following month, effectively rounding up the final result to the next even multiple. ![]() In this version of the formula, we add 15 days to the end date. To calculate months to the nearest whole month, you can adjust the formula as shown below: =DATEDIF(start_date,end_date+15,"m") This means DATEDIF rounds a result down even when it is very close to the next whole month. When calculating months between two dates, DATEDIF always rounds months down to the last complete number of months. ![]() One workaround to this problem is described below. For example, with a start date of July 31 and an end date of September 30, the result is 1, though most people would count this as 2 months. However, when the days don't match, the results can be unexpected. For example, when the days match, the result is an exact multiple of months, as expected. Also notice that results in D13:D16 are a bit quirky, due to how DATEDIF handles end-of-month dates. Notice that the first four results in D5:D8 are exact multiples of months, but the result in D9 has been rounded down to 5 because we are 1 day short of 6 months. The formula in D5, copied down, is: =DATEDIF(B5,C5,"m")Īs the formula is copied down, DATEDIF returns whole months between the start date in column B and the end date in column C. In the worksheet shown below, we are using the DATEDIF function to calculate complete whole months between a start date and an end date. The start dates come from column B and the end dates come from column C. You can use DATEDIF in all current Excel versions, but you must enter the function manually - unlike other functions, DATEDIF will not appear as a suggested function in the formula bar and Excel will not help you with function arguments. For unit, we need to supply "m", because we want to calculate months between dates. See this page on the DATEDIF function for more information about the options available.ĭATEDIF is a "compatibility" function that comes originally from Lotus 1-2-3. DATEDIF (date + dif) is designed to calculate the difference between a start date and an end date in years, months, or days. DATEDIF takes 3 arguments: start_date, end_date, and unit and the generic syntax looks like this: =DATEDIF(start_date,end_date,unit)įor this problem, the start dates come from column B and the end dates come from column C. The solutions described below are based primarily on the DATEDIF function. The best tool for the job is the mysterious DATEDIF function, which is kind of a black sheep in the Excel family. ![]() In addition, there is not a modern Excel function dedicated to the task of calculating months between dates. This is a curiously tricky problem in Excel because the number of days in a month varies, and the rules about how a whole month might be calculated are not obvious. In this example, the goal is to calculate the number of months between two valid Excel dates.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |