We can **convert a date to month and year** using **two simple formulas**. We will use the **TEXT**, **MONTH** and **YEAR functions** to perform this conversion. In the first example, we will use the **TEXT function** and in the second; we will use the **MONTH** and **YEAR** functions.

*Figure 1: Result of convert date to month and year using the TEXT function*

**General formula**

**=TEXT(date,“yyyymm”)**

**Formula**

**=TEXT(B6,"yyyymm")**

**Setting up the Data**

- We will set up the data by inputting hypothetical
**dates**in**Column B** **Column C**is where we will input the formula to arrive at the**result**.

* Figure 2: Setting up the Data*

**Convert date to a month and year with TEXT (B6)**

- We will place our cursor in
**Cell C6** - We will insert the formula below into the
**Cell C6**

**=TEXT(B6,"yyyymm")** - We will
**press the enter key**

* Figure 3: Applying the TEXT function*

- We will click on
**Cell C6**again - We will double click on the fill handle tool (a little plus sign found at the bottom right of
**Cell C6**) and drag down the formula to the other cells.

* Figure 4: Result of using the TEXT function to convert date*

**An alternative; convert date to month and year using the Month and Year functions**

**Step 1:** Converting date to year using YEAR function

- We will find the year and month for our set of hypothetical
**dates** **Columns C and D**will contain our**yea**r and**month**results- In Cell
**C6**, we will enter the formula below and**press the enter key**

**=YEAR(B6)** - Again we use the fill handle tool to drag down the formula to other cells in the given column.

* Figure 5: Result of using the YEAR function to convert date*

**Step 2:** Converting date to month using MONTH function

- In
**Cell D6,**we will enter the formula below

**=MONTH(B6)** - We will
**press the enter key.** - Using the fill handle, we will copy the formula to other cells in the specified column

* Figure 6: Result of using the Month function to convert date*

**Explanation**

**=TEXT(B6,"yyyymm")**

**The TEXT function works on the number format specified and returns with a result in the form of text.**

**=YEAR(B6)**

**The Excel YEAR function will extract the year from the date in the cell and return with the Year as a 4-digit value.**

**=MONTH(B6)**

**We use the Excel MONTH function to extract the month from the specified date and return with the date of the month.**

**Note**

- The Date must be a
**valid Excel date**(serial numbers that begin with 1). - If Date is not valid, then the functions will return with a
**#VALUE error.**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment