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**
Excel Date Conversion from yyyymmdd to mm/dd/yyyy
Re: Excel Date Conversion from yyyymmdd to mm/dd/yyyy
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](https://stackoverflow.com/users/7858537/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(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](https://stackoverflow.com/users/7858537/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.