Go to custom and type in MM/DD/YYYY and it'll work for you

Select the cell or cells that you want to apply the date format to and tap Ctrl+1. When the Format Cells dialog opens, go to the Number tab and select Date from the left hand Category: list. Select 03/14/01 from the Type: list on the right and click OK in the lower right.

Edit: it has occurred to me that your regional setting may default the date formats available from mm/dd/yy to dd/mm/yy, If this is the case, select Custom from the left hand Category: list and manually type in mm/dd/yy to the Type: text box. Click OK to accept the new custom number format.

You can also set the system wide defaults for regional date, currency, etc. through the Control Panel's Region and Language applet.

If the format is *m/dd/yyyy but the cells look the way you show, they are text values, not dates.

To convert them:

• Select the dates (or the entire column).
• On the Data tab of the ribbon, click Text to Columns.
• Select Delimited, then click Next > twice.
• In step 3, select Date, then select DMY from the drop down list next to Date.
• Click Finish.
• Apply date format dd/mm/yyyy to the column.

Formula in B2 is

=DATEVALUE(TEXTJOIN("-",TRUE,CHOOSECOLS(TEXTSPLIT(A1,"/"),3,1,2)))

Working from the inside out:

TEXTSPLIT to split the string into month, day, year

CHOOSECOLS to reorder the strings into year, month, day

TEXTJOIN to join those strings with a - that will be recognized by the DATEVALUE formula

DATEVALUE to turn the strings into a date, and it won't matter whether they are single or double digits for month and date.

• Suppose in cell A2 you have written a date like, 13.12.2017 and it's format is Text.

So to convert into dd/mm/yyyy, you can use the following.

=TEXT(DATEVALUE(RIGHT(A2,4)&"/"&MID(A2,4,2)&"/"&(LEFT(A2,2))),"dd/mm/yyyy")

You get 13/12/2017.

• If A2 has 13.12.20, then you should have to use this formula.

=LEFT(A2,2)&"/"&MID(A2,4,2)&"/"&RIGHT(A2,2)

You get 13/12/20.

In international environments, keep things simple. Just stick with the defaults and don't try to re-invent the wheel.

Use one of the first two date formats from the list. They have an asterisk, which is an indicator that this format will be adjusted to align with the locale to show correct in the language and region that is used when the workbook is opened.

Then set data validation to accept only dates and set the range between 1/1/1900 and 31/12/3000. That should cover your data validation in any region and locale.

Then leave it up to Excel and Windows to sort out the local date format and the way to enter a date correctly.

Edit: to remove all possible problems with locale, set the data validation date range to dates where day and month are interchangeable, like from 1/1/2000 to 1/1/2099. That should take care of all locales.

Try using "Text to columns" functionality to convert "in situ"

Select column of dates then use

Data > Text to columns > Next > Next > under "column data format" choose "Date" > in dropdown select MDY > OK

Now you should have valid dates which you can reformat any way you want, e.g. dd/mm/yyyy

See here

I think item 4 is the one that we need the answer to, as I think that holds they key. If you follow the instructions listed here: How to set up Regional Settings in Windows 10 - Power Community, we want to get to where it shows this: When you get to this screen, what does it show for you for Short and Long Date?

Assuming your date is in A1 you can use this formula to get an actual date: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Then you can use excel's formatting tools to display that date any way you like.

You can convert the value to a date using a formula like this, next to the cell:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Where A1 is the field you need to convert.

Alternatively, you could use this code in VBA:

Sub ConvertYYYYMMDDToDate()
   Dim c As Range
   For Each c In Selection.Cells
       c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
       'Following line added only to enforce the format.
       c.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

Just highlight any cells you want fixed and run the code.

Note as RJohnson mentioned in the comments, this code will error if one of your selected cells is empty. You can add a condition on c.value to skip the update if it is blank.

DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2)) is a brute force attack on the input value. The output is a valid numerical spreadsheet date value, and when you set the date value format accordingy to YYYY-MM-DD, you get the desired output. You can see it here .