Convert Date In Excel To Month And Year
Convert Dates In Excel To Yyyymmdd
Convert Dates In Excel To Calendar

When you work with time series in Excel and import data from sources which use different date format than your operating system, Excel sometimes loads the dates incorrectly. It may swap day and month or not recognize the expression as date and treat it as text.

The first solution to try is the Text to Columns feature – it’s quick and easy; it often works, but sometimes it doesn’t. In such cases you can usually fix the dates using formulas which combine various date and text Excel functions.

If your system date format is different than the date format from the file, dates will be messed up, because excel converts automatically the dates when you open the file. This means that we have to extract data without opening the files (with power query for example and a few other options), to prevent excel from converting dates, or if you. To convert a text date in a cell to a serial number, use the DATEVALUE function. Then copy the formula, select the cells that contain the text dates, and use Paste Special to apply a date format to them. Follow these steps: Select a blank cell and verify that its number format is General. Applies To: Microsoft ® Excel ® 2013 and 2016. Highlight the data range A2 to A4. To convert the above into usable data, select the dates, then select the Data tab, and then Text to Columns. The Convert Text to Columns Wizard is now visible. Select “Next” twice to get to Step 3. In Step 3, select Date and YMD. To convert a serial number to a date, all you need to do is the change the formatting from General (or Number) to a date format (ex. Short Date): Convert Date to Number in Google Sheets. All of the above examples work exactly the same in Google Sheets as in Excel.

In this short tutorial I will show you both these solutions.

On this page:

Example of the Reverse Date Problem

My computer is set to work with dates in the “d/m/yyyy” (UK) format, but I often work with historical financial data in the US format “m/d/yyyy”. For example, I may download the VIX index historical data from the CBOE website (direct file link here) – the current version is a CSV file which starts like this:

It contains the daily open, high, low, close for the VIX index, starting from January 2004.

When I load this file in Excel, it looks like this:

My computer, which is looking for “d/m/yyyy” dates has no way to know that this particular CSV file stores dates in the “m/d/yyyy” format. As a result, 2 January becomes 1 February, 5 January becomes 1 May and so on – the day and month have been reversed.

Moreover, once you get to 13 January (row 10), Excel no longer treats the expressions as dates, because there is no 13th or 14th month.

In sum, in the Date column you end up with two kinds of cells:

Incorrect dates with reversed day and month (for rows where the original date has day <= 12)
Text (for rows where the original day, which Excel thinks should be a month > 12)

This must be fixed before you can do any meaningful work with the data.

Text to Columns Solution

For those not familiar with the Text to Columns feature, let me first mention that one, because when it works, it is much faster than converting the dates using some formulas you build. It is worth trying first.

Select the column with dates and go to Excel main menu / Data / Text to Columns.

A window pops up and you will go through three steps. The first two steps let you decide whether your data is delimited and what the delimiter characters are – these are not important when having only one column selected. The important thing happens in step 3, where you can select the data format.

Check Date and select the format that you think matches the original data source (e.g. MDY if the dates in the original CSV file are something like “m/d/yyyy”). Click Finish and see if the dates have been fixed.

Unfortunately, sometimes this simple solution doesn’t work and it’s time for the harder, formula-based fix, which I introduce below.

Manual Formula-Based Solution

We will create a formula to calculate correct dates from the incorrect ones, of which:

some are dates with reversed day and month, e.g. 1 February, which we must convert to 2 January
some are text not recognized as date, e.g. “1/13/2004” (text), which we must convert to 13 January (date)

Before we start, let’s save the spreadsheet as an Excel file (xlsx or xls), which will enable us to save formulas and formats which a CSV can’t store.

Insert a new column before column B. We will calculate the correct dates in this column.

Fixing the Reverse Day and Month Cells

The first kind of cells (e.g. in rows 3-9 in our example) are recognized as dates in Excel, only with reverse order of day and month. Therefore we can extract the day, month, and year piece using the functions DAY, MONTH, and YEAR, respectively. Only thing to remember is that the result of DAY is really the month in our case and the result of MONTH is the day.

Then we can combine the individual pieces into a correct date, using the DATE Excel function, which takes three arguments: year, month, day – in this order – and returns a date.

In cell B3, let’s calculate the correct date for the first row (which should be 2 January 2004, but cell A3 is 1 February 2004).

The formula in cell B3 is:

… where:

YEAR(A3) is 2004
DAY(A3) is 1, because A3 is 1 February
MONTH(A3) is 2

… so the values of our DATE arguments are:

=DATE(2004,1,2)

Convert Date In Excel To Month And Year

… which returns the correct date: 2 January 2004.

Fixing the Text Cells

The other kind of cells (e.g. rows 10 and below in our example) are not recognized as dates in Excel, because the day (treated as month) is greater than 12 and there is of course no 13th month.

For instance, in cell A10 we have the text “1/13/2004” which we want to convert to the date 13 January 2004.

We will do that using the DATE function again, but this time we must get the three arguments – year, month, day – differently. We must extract them as pieces of the text, using Excel functions LEFT and MID.

Getting Month Using LEFT

The LEFT function extracts the beginning of a string from the first character up to and including the n-th character. It takes two arguments: the original text and the position of the last character to include. Therefore, if we want to get the month from cell A10 (get the “1” from “1/13/2004”), the formula is:

It means: get the first (one) character from the text in cell A10.

Unfortunately, this only works for months 1-9. For dates where the month has two digits (for example, the text is “10/13/2004” = 13 October 2004), we in fact need the formula:

Therefore, we need to make the second argument of the LEFT function dynamic.

We can calculate the desired number of characters (1 for months 1-9 or 2 for months 10-12) by subtracting 8 from the length of the entire string (using the LEN Excel function). The rest of the string after the month has always the same length – 8 characters: “/dd/yyyy”. The day always has two characters, because dates with day 1-12 all go to the first type of cells which we have already fixed above.

The dynamic formula for extracting the month from cell A10 is:

… where the result of LEN(A10)-8 is 1 for single digit months and 2 for months 10-12.

Getting Day and Year Using MID

Unlike month, day and year can’t be obtained using the LEFT function, because they start in the middle of the string. We will use the Excel function MID, which takes three arguments:

=MID(original text, position of first character to return, number of characters)

For example, if we want to get the number 13 (the third and fourth character) from the string “1/13/2004” in cell A10, the formula is:

However, we must make the second argument (starting position) dynamic to work for both single digit and double digit months.

We can use the LEN function again. The first character of day can be calculated as length of the string minus 6. The six characters after that are always “d/yyyy” where d is the second character of day.

The formula for extracting day from cell A10 is:

… where LEN(A10)-6 equals 3 for single digit months and 4 for months 10-12.

We can get year in the same way, this time subtracting 3 from LEN, because the remaining characters after the first character of year are always “yyy”:

The third argument is now 4, because year always has

Convert Dates In Excel