Excel Date Conversion from yyyymmdd to mm dd yyyy

Question

I have been searching for about an hour on how to do this in Excel   I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database  I will be inputting the information back into the SQL Server Database and would like the Dates to match     I have tried Creating a Custom Format  but I am unsure if I even did it Correctly   I found several places where they want to go the other way mm dd yyyy to yyyymmdd  but they have not been helpful   I am unfamiliar with using VBA in any Microsoft Products otherwise I am sure that this would be a simple Task   I have two separate columns that need to be changed   How do I Format the entire column from   float yyyymmdd to a  Date mm dd yyyy

User · Answer

for converting dd mm yyyy to mm dd yyyy   DATE RIGHT a1 4  MID a1 4 2  LEFT a1 2

User · Answer

Do you have ROWS of data  horizontal  as you stated or COLUMNS  vertical    If it s the latter you can use  Text to columns  functionality to convert a whole column  in situ  - to do that   Select column   Data   Text to columns   Next   Next   Choose  Date  under  column data format  and  YMD  from dropdown   Finish      otherwise you can convert with a formula by using   TEXT A1  0000-00-00   0  and format in required date format

User · Answer

Here is a bare bones version    Let s say that you have a date in Cell A1 in the format you described  For example  19760210   Then this formula will give you the date you want    DATE LEFT A1 4  MID A1 5 2  RIGHT A1 2      On my system  Excel 2010  it works with strings or floats

User · Answer

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

User · Answer

Found another  manual  answer which worked well for me   Select the column   Choose Data tab  Text to Columns - opens new box   choose Delimited   Next   uncheck all boxes  use  none  for text qualifier   Next  use the ymd option from the Date dropdown   Click Finish

[excel] Excel Date Conversion from yyyymmdd to mm/dd/yyyy

Examples related to excel

Examples related to excel-2007

Examples related to vba