The math operation causes Excel to try and convert the string to a number. I have a column named "myDate", formatted as a whole number with zero decimal points. #2 right click on it, and select Format Cells from the pop up menu list. Can anyone tell me how to convert 20060701 to 07/01/2006? By joining you are opting in to receive e-mail.Promoting, selling, recruiting, coursework and thesis posting is forbidden.

You can paste formatting multiple times. I have several data tables in with dates in I2 the following format (CYYMMDD), for example 2130731 = 07/31/2013 (MM/DD/YYYY). Hey, Does anyone have a macro to convert a number formatted as YYYYMMDD to a short date format in excel (MM/DD/YYYY)? I'm not sure I understand. Cannot find help in Excel on this matter. I Date 2130731 2130731 2130809 2130731 2130812 2130807 Double-click the Format Painter (left side of Home tab). Esc to stop In a column to the right of the imported data, use this formula:You can also just select the data and use Data / Text To Columns on it, then just specify DATE and choose the YMD option. ","/")) The SUBSTITUTE function is a text function, so cannot convert it to a date on its own.

Get ######. Once we add the comma after the month name, Excel will understand the date, but it still needs a little "kick". The third column is the result of =Text(A1;"yyyymmdd") which is the same result with the second one.

Here are the steps: #1 select the date values that you want to convert the date format.

No extra formulas and data sorted in situ.Please let us know here why this post is inappropriate. If you want to convert the selected range of cells to a given YYYY-MM-DD format, you can use the format cells to change the date format. You must log in or register to reply here. That's why we add zero at the end. And the Format Cells will open. Select a formatted range. Importing text into Excel sets the yyyymmdd field as text format. I don't have a macro, but you can do a quick one-time thing by making a new column with the formula =DATE(LEFT(H2,4),MID(H2,5,2),RIGHT(H2,2)) pasted down the column, and then copy it and do "paste special..." and paste only the values into your current column H. Hope that takes care of things for you, but I'm sure someone else on here can do it with a macro soon.Please note they give different answers for Row 1. Is your number-to-date function running like a champ? All of column H (except the top row) which has a value needs to change to a date. Nice! JavaScript is disabled.

I am having some issues with converting a column formatted as a whole number into a date format. For a better experience, please enable JavaScript in your browser before proceeding. ----------------------------------------------------------------------------Thank you for helping keep Tek-Tips Forums free from inappropriate posts.Join your peers on the Internet's largest technical computer professional community. Does anyone know of a way to convert these values to a workable format in Excel? RE: Excel Convert yyyymmdd text to Date format KenWright (TechnicalUser) 27 Jul 06 01:38 You can also just select the data and use Data / Text To Columns on it, then just specify DATE and choose the YMD option. Date.FromText. =VALUE(SUBSTITUTE(A2,". This is what I see in the file: Column A might look different for me because of regional settings. All full stops are converted to slashes and Excel recognizes the new format as a date. Hello! Creates a date value from a textual representation, text, following ISO 8601 format standard.An optional culture may also be provided (for example, "en-US").. Date.FromText("2010-02-19") // Date, yyyy-MM-dd Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework. If your spreadsheet data is regularly changing, and you want an automated solution for this scenario, you could use the SUBSTITUTE function.