How do I convert a number like 20120103 into a date that Excel can recognize?

Question

I have an 8 digit number that tells me the date, YYYYMMDD. How can I convert this number into a date that Excel will recognize as a date.

Let's assume cell A1 has 20120229 in it...what do I do?

Solution 1

Since you tagged this question VBA, I assume you want an answer in VBA, so here you go:

Dim l As Long
Dim s As String
Dim d As Date

l = Range("A1").Value ' 20120229

' convert it to a string 
s = CStr(l)

' can now use string functions to parse it
d = DateSerial(CInt(Left(s, 4)), CInt(Mid(s, 5, 2)), CInt(Right(s, 2)))
' d is now 29 Feb 2012

' write it back to the sheet
Range("A2").Value = d

Solution 2

Use this formula: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) where A1 is the cell coordinate.

Solution 3

I like being able to select text in excel and call a macro to do the work.

Sub YYYYMMDDToDate()
 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

Comments

[MrPatterns]

Let's say cell A1 is stored in a long variable, lngDate. How would I achieve the same thing in your formula using VBA? I'd like to store the date in variable dtDate (Dim dtDate as Date).

[Máté Gelei]

Oh, I didn't realize it had to be done in VBA, thought it was optional, sorry. Someone else answered it, though.

[lori_m]

FYI To insert formulas into VBA you can use the evaluate function or just enclose by []. This can be useful when you want to operate on arrays but you need to take care to ensure the result is also an array. For example to convert the range a1:a9 to dates you could enter: [a1:a9]=[if({1},text(a1:a9,"0000-00-00"))]

[Jean-François Corbett]

@MattDonnan: Thanks! But hmm... I think DateSerial returns a value of Date type which is not in any particular format?... It's when you convert it to a String that it acquires a format.

[Matt Donnan]

@Jean-FrançoisCorbett What I mean is, for example if you are using english uk then it will return dd/mm/yyyy whereas if using english us it returns mm/dd/yyyy

[Jean-François Corbett]

@MattDonnan: Well, coercing the Date type to a String will return a date formatted to your machine's locale. So really, it's the CStr function that does that (whether you apply it explicitly, or implicitly by saying s = d), not the Date type itself.