Manually extracting month and year from an insurmountable maze of digits is a time-consuming and error-prone endeavor.
If you, too, find yourself nodding in empathy, fear not! In this guide, we will teach you how to convert the date (dd/mm/yyyy) to month and year in Excel.
So, say goodbye to manual drudgery, and embrace the efficiency of Excel’s date conversion techniques.
Understanding Date Formats in Excel
Excel internally represents dates as serial numbers, where each date is assigned a unique numeric value.
In this system, the date January 1, 1900, is assigned the serial number 1, while each subsequent date increments the serial number by one day. For example, January 2, 1900, has a serial number of 2, January 3, 1900, has a serial number of 3, and so on.
Moreover, Excel also considers time as a decimal portion of the serial number. Each day is divided into 24 hours, and each hour is divided into 60 minutes, and each minute is divided into 60 seconds. Therefore, a fraction of a day represents the time portion of the date.
For instance, if a cell in Excel contains the date “January 31, 2023,” Excel internally stores it as the serial number 44404. The number 44404 represents the number of days that have passed since January 1, 1900. If the cell contains a date with a time component, such as “January 31, 2023, 3:30 PM,” the serial number would be 44404.6458333333, where 0.6458333333 represents the time portion of the date, corresponding to 3:30 PM in decimal format (0.6458333333 = 15:30 / 24).
In Excel, there are several common date formats that you may encounter when working with your data. Here are some of the most frequently used date formats in Excel:
| Date Format | Example |
| Short Date Format | 07/31/2023 |
| Long Date Format | Monday, July 31, 2023 |
| Month-Day-Year Format | July 31, 2023 |
| Day-Month-Year Format | 31 July 2023 |
| Custom Date Formats | 2023-07-31 or 31-Jul-2023 |
| Date and Time Format | July 31, 2023, at 3:30 PM |
List of some date formatting codes are mentioned below for you to understand how you can deal with date conversions.
| Date Formatting Codes | Description | Example |
| m | Represents month in a single digit | July is represented as 7. |
| mm | Represents month in dual digit | July is represented as 07. |
| mmm | Represents first three letters of the month’s name | July is represented as Jul. |
| mmmm | Represents full month name | July is represented as July. |
| yy | Represents two-digit year. | 2023 is represented as 23. |
| yyyy | Represents year fully in four-digits. | 2023 is represented as 2023. |
How To Convert Date To Month
To convert a date to a month in Excel, you can use the MONTH function or the TEXT function. Or, Additionally, you can use a custom formatting as well. Let’s break down the topic to elaborately discuss the steps.
1. Use the MONTH Function
The syntax of the MONTH Function is =MONTH(serial_number). You can refer to any cell or directly input the date inside the function.
Look at our dataset. We typed in the =MONTH function and referred to the cell D4. So, our overall formula was =MONTH(D4). The result showed the month number.
The only drawback of this step is that the MONTH function only shows the month number, not the full month name. You have to combine the DATEVALUE function with a TEXT function. Let’s look at the next step for elaborate details.
2. Use The Combination of DATEVALUE And TEXT Function
The DATEVALUE function is used in Excel to convert a date represented as text into a valid Excel date value. In Excel, dates are internally stored as serial numbers (as mentioned earlier). This function works great if merged with the TEXT Function.
Our formula to convert date to month will be:
Here,
- MONTH(D4) extracts the month number from cell D4.
- “1-” & MONTH(D4) & “-2022” concatenates the extracted month number with “1-” and “-2022” to form a date string like “1-2-2022”.
- DATEVALUE(“1-” & MONTH(A1) & “-2022”) converts the date string into a valid Excel date value.
- TEXT(…, “mmmm”) formats the date value as text using the “mmmm” format code, which displays the full month name.
So, the result of the formula will be the full month name as shown in the image.
Converting Date To Year
To convert a date to year, you have to use the YEAR Function. Yes, there are some other alternatives as well. For that, you can check our post on how to extract a year from a date in Excel.
The YEAR function works similarly to the MONTH function. It extracts the year in a desired format from a given range.
We used the formula on the E4 cell and referred the cell to D4. So, our formula to convert a date to a year became:
Result: Four-digit resulting year was shown. Example: 2023, 2019, etc.
How To Convert Date to Month and Year in a Single Cell
We will show you two different approaches for this section. The first one will be on the basis of a formula and the latter will be a formula without using any function.
First of all, we will need the MONTH and The YEAR Function altogether and then use the formula below.
So, here are the ways to convert date to month and year into a single cell:
1. Apply The CONCATENATE Function
The CONCATENATE function is used to combine multiple text strings into one string. Its syntax is-
Here’s an example of using the CONCATENATE function:
Suppose you have the following data in Excel in cell A1:A3 and B1:B3:
| Hey | Guys |
| Excel | is |
| so | Great |
In cell C1, you can concatenate the values from columns A and B with the formula:
This would result in the following output in cell C1:
Hey Guys
So, applying this formula in our current template, it would be something like-
Here, we used a delimiter to separate the month and year from merging. If we didn’t use the delimiter, the result would look something like this- 22022.
2. Use the And (&) Operator
The & operator helps to join texts and works as an alternative to the TEXTJOIN Function. After converting the date to month and year separately, we will now combine the numbers using this operator.
In cell H4, we typed, =E4&”/”&F4 and then pressed Enter. We also set the (/) as a delimiter.
In this way, we can convert any date format to month and year, both separately and combinedly in Excel.
Conclusion
In this post, we covered how to convert a date (mm/dd/yyyy) or (dd/mm/yyyy) format to month and year in Excel using three formulas. The functions used were MONTH, YEAR, and CONCATENATE.
We also looked at another method that doesn’t require any function. Personally, our favorite way is using the CONCATENATE function because it can join any formatted texts.
