Sometimes, you may need to get the previous month name of a given month in Excel, for example, to create a report, a calendar, or a dashboard. There are different ways to achieve this task using Excel formulas, depending on the format and source of the given month. In this article, we will show you some common methods and examples of how to get the previous month name of a given month in Excel.
To get the previous month name of a given month in Excel, we need to use two main components: a function that can extract or convert the month number from the given month, and a function that can format the month number as a month name. The most common functions that can do these tasks are:
- The
MONTHfunction, which returns the month number (from 1 to 12) of a given date or text value that represents a date. - The
TEXTfunction, which converts a value to text in a specified format. We can use the format code “mmmm” to get the full month name, or “mmm” to get the abbreviated month name. - The
EOMONTHfunction, which returns the last day of the month that is a specified number of months before or after a given date. - The
DATEfunction, which returns a date based on the year, month, and day values that you provide. - The
TODAYfunction, which returns the current date. - The
DAYfunction, which returns the day of the month (from 1 to 31) of a given date.
Using these functions, we can create formulas that can get the previous month name of a given month in Excel. However, the exact formula will depend on the format and source of the given month. In the next section, we will explain the procedures and examples of some common scenarios.
Examples
Scenario 1: The given month is a date value
If the given month is a date value, such as 01/01/2023 or 1-Jan-2023, we can use the following formula to get the previous month name:
=TEXT(EOMONTH(A1,-1),"mmmm")
where A1 is the cell that contains the given month.
This formula works by using the EOMONTH function to return the last day of the previous month, and then using the TEXT function to format it as a month name. For example, if A1 is 01/01/2023, the formula will return December.
Scenario 2: The given month is a text value
If the given month is a text value, such as January or Jan, we can use the following formula to get the previous month name:
=TEXT(DATE(2000,MONTH(A1&1)-1,1),"mmmm")
where A1 is the cell that contains the given month.
This formula works by appending a 1 to the given month to create a valid date, such as January1 or Jan1, and then using the MONTH function to return the month number. Then, we subtract 1 from the month number and use the DATE function to create a date in the year 2000 with the previous month number. Finally, we use the TEXT function to format it as a month name. For example, if A1 is January, the formula will return December.
Scenario 3: The given month is the current month
If the given month is the current month, we can use the following formula to get the previous month name:
=TEXT(TODAY()-DAY(TODAY()),"mmmm")
This formula works by using the TODAY function to return the current date, and then subtracting the day of the month from it. This will give us the last day of the previous month. Then, we use the TEXT function to format it as a month name. For example, if today is 01/01/2023, the formula will return December.
Other Approaches
Besides the formulas we have shown, there are other possible approaches to get the previous month name of a given month in Excel. For example, you can use:
- The
CHOOSEfunction, which returns a value from a list based on a given index number. You can use this function to create a list of month names and then use a formula to calculate the index number of the previous month. - The
EDATEfunction, which returns a date that is a specified number of months before or after a given date. You can use this function to get the date of the previous month and then use theTEXTfunction to format it as a month name. - A custom VBA function, which allows you to write your own code to get the previous month name of a given month. You can use the
MonthNamefunction in VBA, which returns the month name of a given month number. You can also use theFormatfunction in VBA, which works similarly to theTEXTfunction in Excel.
