converting number to date

[daveyc18]

ok so the data i pull in has the date in this general form:

20190502 .....I want to covert that easily into a date format so that I can compare the number of days between the pulled in date and another date

is there an efficient way to do this? ideally via VBA.

 

[Tetra201]

Try these steps:

- Select the column with imported dates
- Go to Data --> Text to Columns
- Select Delimited on Step 1 of 3
- Select Tab on Step 2 of 3
- Select Date: YMD on Step 3 of 3
- Finish

 

[CalcSux78]

I agree with Tetra's method.

If you insist on vba:

Sub Num2Date()
Dim cel As Range
Dim temp$


For Each cel In Selection
    temp = cel.Value
    cel = DateSerial(CLng(Left(temp, 4)), CLng(Mid(temp, 5, 2)), CLng(Right(temp, 2)))
Next cel
End Sub

 

[daveyc18]

Try these steps:

- Select the column with imported dates
- Go to Data --> Text to Columns
- Select Delimited on Step 1 of 3
- Select Tab on Step 2 of 3
- Select Date: YMD on Step 3 of 3
- Finish

thanks, tetra...duh on my part...not thinking straight today

 

[Rick Rothstein]

I agree with Tetra's method.

If you insist on vba:

Sub Num2Date()
Dim cel As Range
Dim temp$


For Each cel In Selection
    temp = cel.Value
    cel = DateSerial(CLng(Left(temp, 4)), CLng(Mid(temp, 5, 2)), CLng(Right(temp, 2)))
Next cel
End Sub

Apparently the OP does not want a macro solution, but I thought you might find this non-looping macro to be of some interest (note that it handles blank cells whereas your code errors out if a blank cell is encountered in the selection)...

[table="width: 500"]
[tr]
	[td]Sub NumToDate()
  Selection = Evaluate(Replace("IF(@="""","""",0+TEXT(@,""0000-00-00""))", "@", Selection.Address))
  Selection.NumberFormat = "m/d/yyyy"
End Sub[/td]
[/tr]
[/table]