How To Stop Excel From Making Dates

Whenever you enter a date in a cell, Excel automatically recognizes the format and converts the cell to a date prison cell.

So, Excel knows which part of the appointment you entered is the month, which is the yr and which is the twenty-four hour period.

This can be quite helpful in many ways. One particular benefit of this adequacy of Excel is that it lets you display the date in any format y'all desire. It even lets yous extract parts of the date that yous need.

For case, yous might discover the day function of the date irrelevant and just need to display the month and year.

Since Excel already understands your engagement, yous tin hands extract just the month and year and display it in any format you like.

In this tutorial, we are going to see iii means in which yous can convert date to month and year in Excel.

The Sample Data

Throughout this tutorial, we are going to be using the following set of dates. We will be converting these dates to month and year in Excel:

When working with dates, first and foremost, information technology is important to recognize the original format your Excel dates are in. For example, in the United states format, dates normally brainstorm with the month and stop with the year (mm/dd/yyyy).

In the Great britain and other countries, dates begin with the day and stop with the year (dd/mm/yyyyy). In nonetheless other places like China, Iran, and Korea, the gild is completely flipped (yyyy/mm/dd).

Depending on your calculator's engagement settings Excel volition treat parts of your engagement differently. And so when entering the engagement, make sure you check the format and enter the appointment in the right lodge. Y'all don't desire the appointment 2/10 to be treated as Oct, the 2nd instead of Feb, the 10th!

Convert Appointment to Month and Twelvemonth using the MONTH and YEAR role

The Month and YEAR functions can aid yous excerpt merely the month or yr respectively from a engagement cell. In order for this method to work, the original date (on which you want to operate) must be a valid Excel date. If not, and then both these functions will return a #VALUE error.

Let'southward come across how you can excerpt the calendar month from our sample information:

Click on a blank cell where you lot want the month to be displayed (B2)
Type: =MONTH, followed by an opening bracket (.
Click on the first cell containing the original date (A2).
Add together a closing subclass )
Press the Return primal.
This should display the calendar month of the yr respective to the original engagement. Copy this to the rest of the cells in the cavalcade by dragging downward the fill up handle or double-clicking on it.

You lot will see column B populated by the calendar month of the yr for all the dates of column A.

At present allow's come across how y'all can extract the year from the same dataset:

Click on a blank cell where yous desire the year to be displayed (C2)
Type: =Yr, followed by an opening bracket (.
Click on the first cell containing the original date (A2).
Add a closing bracket )
Printing the Return fundamental.
This should display the year corresponding to the original engagement. Copy this to the remainder of the cells in the column by dragging down the fill handle or double-clicking on it.

Yous will see cavalcade C populated by the year respective to all the dates of column A.

Now let's encounter how y'all can combine the results of the two to brandish both month and year in a nice format.

Allow united states of america say you want to display both calendar month and year equally "two-2018" for the date "02/10/2018", and want to follow this pattern for all the dates.

Click on a bare prison cell where you want the new engagement format to be displayed (D2)
Type the formula: =B2 & "-" & C2. Alternatively, you lot can blazon: =Calendar month(A2) & "-" & YEAR(A2).
Press the Return key.
This should display the original appointment in our required format. Copy this to the rest of the cells in the column past dragging down the fill handle or double-clicking on it.

Now all your cells in column D2 take the new format:

Alternatively, if you want to display your month and year as 2/2018 instead, you just need to replace the "-" in-between with a "/". In this fashion, you can display the month and year in any format that you like.

Finally, if you lot want to but continue the converted values and desire to remove the original dates and any intermediate columns that you created yous need to first convert the formula results into constant values.

For this, copy the cells of column D and paste them as values in the same column (Right-click and select Paste Options->Values from the Popup menu). Now you can get ahead and delete columns A to C. You will be left with simply the converted values that have the month and yr.

Although this is quite an easy and intuitive way to convert dates to month and yr, it is a less pop method. This is because this method does not provide a lot of flexibility compared to the other two methods we will show next.

Catechumen Engagement to Month and Year using the TEXT Role

The TEXT function in Excel converts any numeric value (similar appointment, time, and currency) into text with a specified format.

The syntax of the TEXT office is:

= TEXT (value, format_code)

Hither,

value is the numeric value or reference to the cell that you want to catechumen
format_code is the format y'all want to convert the cell into

In the above instance, the TEXT office applies the format_code that you specified on the value and returns a text cord with that format. For case, if you lot have a date "two/10/2018" in cell A2, and then =TEXT(A2, "mm/yyyy") will render "02/2018"

There are a number of format codes that you tin utilize. We have enlisted below the bones building blocks for the format codes:

Format Codes for Year:

You lot can use the following 2 bones format codes to represent year values:

yy – two-digit representation of year (e.thou. 20 or 12)
yyyy – four-digit representation of year (due east.g. 2020 or 2012)

So, if you employ =TEXT(A2, yy) in our example dataset, it volition render "xviii".

If you employ =TEXT(A2, yyyy), then information technology will render "2018".

Format Codes for Month of the Yr:

You tin use the following four bones format codes to represent month values:

g – one or two-digit representation of the month (eg; 8 or 12)
mm – 2-digit representation of the calendar month (eg; 08 or 12)
mmm – calendar month abbreviated in three letters (eg: Aug or December)
mmmm – month expressed with the full name (eg: August or December)

And then, if you employ =TEXT(A2, m) in our example dataset, information technology volition return "two".

If you apply =TEXT(A2, mm), then it will render "02".
If you lot utilize =TEXT(A2, mmm), then it will return "Feb".
If you employ =TEXT(A2, mmmm), then it will return "Feb".

Allow us come across how we can apply the TEXT function to our sample dataset to convert all the dates to different formats.

Nosotros volition offset see how to convert the dates in column A to the format shown in column B in the image below:

Below are the steps to change the appointment format and only get calendar month and twelvemonth using the TEXT function:

Click on a blank cell where you lot want the new date format to be displayed (B2)
Blazon the formula:
```
=TEXT(A2,"m/yy")
```
Press the Render primal.
This should display the original date in our required format. Re-create this to the rest of the cells in the column by dragging down the fill handle or double-clicking on information technology.
Copy this column'southward formula results past pressing CTRL+C or Cmd+C (if y'all're on a Mac).
Right-click on the column and form the popup menu that appears, printing Paste Values from the Paste Options.
This will shop the formula results as permanent values in the same cavalcade. Now you can become alee and remove column A if yous want to.

The format code that you lot put in the formula (at footstep 2) will vary according to the format you desire your month and twelvemonth to appear in. Here are the format codes forth with the type of result yous will become when practical to cell A2:

Function	Result
=TEXT(A2, "mm/yy")	02/xviii
=TEXT(A2, "mm-yy")	02-eighteen
=TEXT(A2, "mm-yyyy")	02-2018
=TEXT(A2, "mmm, yyyy")	Feb, 2018
=TEXT(A2, "mmmm, yyyy")	February, 2018

So you see, yous can use the TEXT part to convert your dates to any format of your pick. All you need to do is change the format lawmaking co-ordinate to your requirement.

Annotation: Using this formula, your date gets converted to a text format. If you want to convert it dorsum to a date format, y'all need to use the Format Cells feature.

Convert Appointment to Month and Year using Number Formatting

Excel's Format Cell feature is a versatile ane that lets y'all perform dissimilar types of formatting through a single dialog box.

Here's how you lot can convert the dates in our sample dataset to different formats.

Select all the cells containing the dates that you lot want to catechumen (A2:A6).
Correct-click on your selection and select Format Cells from the popup bill of fare that appears. Alternatively, you can select the dialog box launcher in the Number grouping under the Home tab.
This will open up the Format Cells dialog box. Click on the Number tab
Nether Category on the left side of the box, select the Date option.
This will display a number of formatting options for date on the right side.
Select the format that you want. For instance, if you desire to display the first engagement in the format "Feb-xviii", then select the matching format option.
If y'all don't find an option for the format yous want to employ, then you tin employ the Custom pick from the Category list on the left. This lets you convert the jail cell to a custom format.
Look if your format is available among the date format codes under Type. If not, you lot can type in your format code in the input box only below Blazon. And so, if you lot want to display the beginning month in the format "two/eighteen", and then type "chiliad/yy".
Click OK to shut the Format Cells dialog box.

All your selected cells should now exist formatted to your required format.

This method differs from the previous one (using the TEXT part) in four ways:

With this method, you can perform the operation directly on the original cells.
You tin can get your conversion done in i go. Then y'all don't need to accept a dissever cell to enter the formula, then paste by value so delete the original cells (as y'all would need to if using the TEXT office).
This method changes just the format of the original engagement, the underlying appointment, however, remains the same. Then if you want to later recover the original appointment value (along with the day), you can easily access it. With the TEXT function method, nevertheless, the original date value is lost because the conversion changes the entire value of the engagement.
The results y'all get from using this method are of type Appointment, rather than Text, and then you tin perform date operations on them straight without having to convert them.

These were 3 ways in which y'all tin convert appointment to month and year in Excel. Using these you lot can convert your date to any format you need.

We hope you institute our methods useful and that y'all will utilize it to your own Excel data.

Other Excel tutorials you may like: