How to convert D/M/YYYY date to M/D/YYYY or vice versa

by | May 30, 2018 | Excel Tips & Tricks

How to convert D/M/YYYY date to M/D/YYYY date or vice versa

Data formats differ by locations in the world. Once you understand the syntax of dates you can easily convert them from one locale to the other. In this article, we will cover the most standard date conversion in Excel: converting D/M/YYYY to M/D/YYYY.

Syntax

first separator: =FIND("/", date value)

second separator: =FIND("/", date value, first separator + 1)

day: =LEFT(date value, first separator-1)

month: =MID(date value, first separator+1, second separator - first separator - 1)

year: =RIGHT(date value, LEN(date value) - second separator)

=DATE(year, month, day)

Steps

  1. Locate the position of the first separator by using the FIND function =FIND("/",$B3)
  2. Locate the second separator starting search after the first separator =FIND("/",$B3,C3+1)
  3. Get the day from date by using parsing the numerals before the first separator =LEFT($B3,C3-1)
  4. The month value is between the first and second separator =MID($B3,C3+1,D3-C3-1)
  5. The last numerals after the second separator represents year =RIGHT($B3,LEN($B3)-D3)
  6. Combine day, month and year values as a date value =DATE($G3,$F3,$E3)

How

Typically, day, month and year values are separated by a character like slash (/) or period (.). The logic between parsing the date is to locate the separator and get the numerals before, between or after them.

Locating Separators

To find the separators you can use FIND or SEARCH functions. Both return the start position index of the character or string you are looking for. The difference between them are case-sensitivity which is not a problem while searching a special character. They have two mandatory and one optional arguments. The mandatory ones are the string you are searching and the cell that contains the search string. The optional one specifies the character at which to start the search that is needed to locate the second separator.

First separator: =FIND("/",$B3)

Second separator: =FIND("/",$B3,C3+1)

Parsing

In order to parse the day, month and year values, we need the basic text functions like LEFT, MID and RIGHT.  They can grab the string from reference cell or string. They require values for how many characters to parse. The next step is to find the locations of the day, month and year values by using the +1 and -1 operators. Additionally; the LEN function is used to help the RIGHT function. The LEN function returns the character count of the specified text which is used by the RIGHT function.

Day: =LEFT($B3,C3-1)

Month: =MID($B3,C3+1,D3-C3-1)

Year: =RIGHT($B3,LEN($B3)-D3)

Combining

The last step is to combine day, month, and year values. Excel has a perfect function to convert them into a valid date. The DATE function simply combines specified day, month and year values and returns a date value.

=DATE($G3,$F3,$E3)

Also see article how to use data information from date data in Excel.