Date format

%3CLINGO-SUB%20id%3D%22lingo-sub-2317444%22%20slang%3D%22en-US%22%3EDate%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317444%22%20slang%3D%22en-US%22%3E%3CP%3EHi......Can%20someone%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20excel%20with%206k%20entries%20and%20the%20date%20is%20in%20American......YYYY-MM-DD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20format%20the%20cell%20to%20English%20DD-MM-YYYY.%26nbsp%3B%20i%20highlighted%20and%20selected%20Columns%20to%20text%20to%20change%20to%20Date%20instead%20of%20general.%26nbsp%3B%20nothing%20seems%20to%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2317444%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2317537%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1043689%22%20target%3D%22_blank%22%3E%40MarieSpence%3C%2FA%3E%26nbsp%3BUse%20Text-to-Columns%20on%20the%20Data%20ribbon.%20In%20step%203%2C%20select%20%22Date%3A%22%20and%20%22YMD%22%20and%20Finish.%20This%20will%20convert%20the%20dates%20to%20a%20format%20that%20matches%20your%20local%20settings.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20YYYY-MM-DD%20is%20the%20ISO%20date%20format.%20In%20the%20US%2C%20they%20use%20MM-DD-YYYY.%20If%20that%20is%20what%20you%20intended%20to%20write%2C%20choose%20MDY%20in%20step%203%2C%20as%20described%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi......Can someone help

 

I have a excel with 6k entries and the date is in American......YYYY-MM-DD

 

I tried to format the cell to English DD-MM-YYYY.  i highlighted and selected Columns to text to change to Date instead of general.  nothing seems to work

2 Replies

@MarieSpence 

1. Format a date and time field

The date and time data types have a wide variety of formats to help meet your unique circumstances. When formatting you have three choices: keep the default formats, apply a predefined format, or create a custom format. When you apply a format to a table field, that same format is automatically applied to any form or report control that you subsequently bind to that table field. Formatting only changes how the data is displayed and does not affect how the data is stored or how users can enter data.

 

2. How to change American date format in Excel?

Change date formats with the Format Cells feature

With this Format Cells function, you can quickly change other date formats to America date format.

Change date formats with formula

You can use a formula to convert the date format according to following steps:

1. In a blank cell, input the formula =TEXT(A1, "mm/dd/yyyy"), in this case in cell B1.

Of course there is also the VBA solution, which is not analyzed here.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@MarieSpence Use Text-to-Columns on the Data ribbon. In step 3, select "Date:" and "YMD" and Finish. This will convert the dates to a format that matches your local settings.

 

By the way, YYYY-MM-DD is the ISO date format. In the US, they use MM-DD-YYYY. If that is what you intended to write, choose MDY in step 3, as described above.