Excel Date Conversion from yyyymmdd to mm/dd/yyyy

by

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.

More Related Contents:

  1. How to add a custom Ribbon tab using VBA?
  2. filter out multiple criteria using excel vba
  3. Closing Excel Application using VBA
  4. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  5. Cannot VBA write data to cells in Excel 2007/2010 within a function
  6. VBA equivalent to Excel’s mod function
  7. Excel VBA to match and line up rows
  8. Excel: the Incredible Shrinking and Expanding Controls [closed]
  9. How to programmatically code an ‘undo’ function in Excel-Vba? [duplicate]
  10. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  11. Delete all data rows from an Excel table (apart from the first)
  12. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  13. Excel VBA – How to add dynamic array formula
  14. Compare double in VBA precision problem
  15. Row count on the Filtered data
  16. VBA – Write all possible combinations of 4 columns of data [duplicate]
  17. Remove duplicates from array using VBA
  18. How to automatically insert a blank row after a group of data
  19. Get File Path (ends with folder)
  20. Email a single attachment from folder of files each to a different person
  21. Bad data selection using VBScript , on date field values
  22. Sending object array to variant works before August 2019 Windows update, but fails after update
  23. Find last row in range
  24. Differences between Excel’s Form Controls & ActiveX Controls
  25. Excel VBA User Defined Function that counts cells with conditional formatting
  26. simple vba code gives me run time error 91 object variable or with block not set
  27. Excel VBA – select multiple columns not in sequential order
  28. Application.WorksheetFunction.Match method
  29. Excel – Using COUNTIF/COUNTIFS across multiple sheets/same column
  30. Excel telling me my blank cells aren’t blank

Leave a Comment