Now you can go ahead and delete columns A to C. In this way, you can display the month and year in any format that you like.įinally, if you want to just keep the converted values and want to remove the original dates and any intermediate columns that you created you need to first convert the formula results into constant values.įor 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 all your cells in column D2 have the new format:Īlternatively, if you want to display your month and year as 2/2018 instead, you only need to replace the “-“ in-between with a “/”. Copy this to the rest of the cells in the column by dragging down the fill handle or double-clicking on it. This should display the original date in our required format.Alternatively, you can type: =MONTH(A2) & “-” & YEAR(A2). Click on a blank cell where you want the new date format to be displayed (D2).Let us say you want to display both month and year as “2-2018” for the date “”, and want to follow this pattern for all the dates. Now let’s see how you can combine the results of the two to display both month and year in a nice format. You will see column C populated by the year corresponding to all the dates of column A. This should display the year corresponding to the original date.Type: =YEAR, followed by an opening bracket (.Click on a blank cell where you want the year to be displayed (C2).Now let’s see how you can extract the year from the same dataset: You will see column B populated by the month of the year for all the dates of column A.
If not, then both these functions will return a #VALUE error. In order for this method to work, the original date (on which you want to operate) must be a valid Excel date. The MONTH and YEAR functions can help you extract just the month or year respectively from a date cell. You don’t want the date 2/10 to be treated as October, the 2nd instead of February, the 10th! Convert Date to Month and Year using the MONTH and YEAR function So when entering the date, make sure you check the format and enter the date in the correct order.
In still other places like China, Iran, and Korea, the order is completely flipped (yyyy/mm/dd).ĭepending on your computer’s date settings Excel will treat parts of your date differently. In the UK and other countries, dates begin with the day and end with the year (dd/mm/yyyyy). For example, in the US format, dates usually begin with the month and end with the year (mm/dd/yyyy). When working with dates, first and foremost, it is important to recognize the original format your Excel dates are in. We will be converting these dates to month and year in Excel: Throughout this tutorial, we are going to be using the following set of dates.
0 kommentar(er)
