Explanation
In this example, the goal is to get and display the month name from any given date. There are several ways to go about this in Excel, depending on whether you want to extract the month name as text, or just display a valid Excel using the month name.
To extract the month name from a date as text , you can use the TEXT function with a custom number format like “mmmm”, or “mmm”. In the example shown, the formula in cell C4 is:
=TEXT(B4,"mmmm") // returns "April"
The TEXT function converts values to text using the number format that you provide. Note that the date is lost in the conversion: only the text for the month name remains. To extract an abbreviated month name like “Jan”, “Feb”, etc. use a slightly different number format:
=TEXT(B4,"mmm") // returns "Apr"
Display month name
If you only want to display a month name, you don’t need a formula – you can use a custom number format to format the date directly. Select the date and navigate to Format cells (Ctrl + 1 or Cmd +1), then select Custom and enter one of these custom formats:
"mmm" // "Jan"
"mmmm" // "January"
Excel will display only the month name, but it will leave the date value intact. You can also use a longer date format like this:
"mmmm d, yyyy" // "January 1, 2021
CHOOSE option
For more flexibility, you create your own month names with the CHOOSE function and MONTH function like this:
=CHOOSE(MONTH(date),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Enter the month names you want to return (customized as you like) as values in CHOOSE, after the first argument, which is entered as MONTH(date). The MONTH function will extract a month number, and CHOOSE will use this number to return the “nth” value in the list. This works because MONTH returns a number 1-12 that corresponds to the month name.
CHOOSE is more work to set up, but it is also more flexible, since it allows you to map a date to any values you want (i.e. you can use values that are custom, abbreviated, not abbreviated, in a different language, etc.).
Explanation
In this example, the goal is to calculate the number of months between two valid Excel 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 addition, there is not a modern Excel function dedicated to the task of calculating months between dates. I have no idea why. The best tool for the job is the mysterious DATEDIF function, which is kind of a black sheep in the Excel family.
The DATEDIF function
The solutions described below are based primarily on the DATEDIF function. 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)
For this problem, the start dates come from column B and the end dates come from column C. 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.
DATEDIF is a “compatibility” function that comes originally from Lotus 1-2-3. 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.
Calculate complete whole months
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. The formula in D5, copied down, is:
=DATEDIF(B5,C5,"m")
As the formula is copied down, DATEDIF returns whole months between the start date in column B and the end date in column C. 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. Also notice that results in D13:D16 are a bit quirky, due to how DATEDIF handles end-of-month dates. For example, when the days match, the result is an exact multiple of months, as expected. However, when the days don’t match, the results can be unexpected. 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. One workaround to this problem is described below.
Calculate nearest whole months
When calculating months between two dates, DATEDIF always rounds months down to the last complete number of months. This means DATEDIF rounds a result down even when it is very close to the next whole month. To calculate months to the nearest whole month, you can adjust the formula as shown below:
=DATEDIF(start_date,end_date+15,"m")
In this version of the formula, we add 15 days to the end date. 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. 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. The screen below shows how the original DATEDIF formula compares to the modified version:
Calculate decimal months
As 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
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:
Keep in mind that the behavior of this formula depends on how YEARFRAC works. See this page for more information.
Alternative formula to calculate months touched by dates
In some cases, you may want to count how many months are “touched” by a given date range. The generic formula for this calculation looks like this:
=(YEAR(C5)-YEAR(B5))*12+MONTH(C5)-MONTH(B5)+1
This formula uses the YEAR function and the MONTH function to work out a count in two parts. First, the months associated with a year change are calculated:
(YEAR(end)-YEAR(start))*12 // months due to year change
This code only affects the count if the start year and end year are different. If the start year and end year are the same, the result is zero. Next, the formula subtracts the start month from the end month and adds 1 to calculate the remaining months:
MONTH(end)-MONTH(start)+1 // remaining months
Notice this calculation means that any month difference will result in a positive number because the day of the month is completely ignored. Finally, the two parts of the formula are added together to get a total month count. The screen below shows the formula in action, compared to the original DATEDIF formula above:
As you would expect, this formula tends to increase the month count for a date range, since even a small portion of a month will be included in the result. You can see this effect if you compare results from the original DATEDIF formula in column E to results from the alternative formula in column F.
