As the formula is copied down the column, it returns the correct number for each month. Working from the inside out, we start by concatenating the name in cell B5 to the number 1: This expression returns a string like “January1”, “February1”, “March1”,and so on. It turns out, that if we pass a date fragment like this into the MONTH function, it will coerce the string to a valid date, using the current year. As I write this, the year is 2021, so the result for January is the date January 1, 2021, represented in Excel as the serial number 44197. After this date has been created, the MONTH function returns the correct month number for the date that was created using concatenation. In each case, the actual date value is a “throwaway”, used only as a convenient value to pass into the MONTH function. In B5 the evaluation works like this (in the year 2021):
Date evaluation
This example is a bit tricky in that the evaluation of the date inside the MONTH function is automatic. If you use the expression that concatenates the month name to 1 outside the MONTH function, you’ll need to add an extra step to get Excel to convert the text to a date. The DATEVALUE function or adding zero are both good options: Both will return a date serial number, which must then be formatted as a date.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
