datemicrosoft excel
I have a column of date-like string values in the format yyyy-mm-dd, such as 2011-Sep-13. I need to convert these to Excel date serial numbers so that I can use them in formulas.
DATEVALUE isn't able to recognize this format; I just get #VALUE! when I try. It also does not take a custom date format (unlike most programming languages).
How can I convert arbitrary date formats into Excel date values?
What you want is to use a custom format. Just type it in the box.
Select the cells and run:
Sub DateFixer()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For Each r In Selection
v = r.Text
r.Clear
r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
Next r
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Best Answer
You'll need to do string parsing, then pass an appropriate formatted value into
DATEVALUE– something like this:(edit: updated formula - tested and it works for me on one example - may need to refine depending on your input)