Date to Text reversed.

[Edgarvelez]

Hi All,

Need a code

The date format is day month Year and the range is dynamic in column A.

Example 6-Nov result would be 20231106 in Column A

Thanks in advance

 

[Peter_SSs]

Sorry, I don't understand that.

 

[Edgarvelez]

full code copy from

and paste result like this, Note the format that I have in column C is general and I have to keep that format.

 

[Peter_SSs]

How does 4-Dec in 'Dashboard' become 20231206 in the other sheet? Similarly for 5-Dec becoming 20231208?

In your previous post the dates don't seem to match either with 3-Dec becoming 20231202

 

[Edgarvelez]

sorry my typo errors. it's supposed to be as follows:
20231203
20231204
20231205

 

[Peter_SSs]

Try this.

Sub Test()
  Dim a As Variant
  Dim i As Long
  
  With Sheets("Dashboard")
    a = .Range("B21", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    a(i, 1) = Format(a(i, 1), "yyyymmdd")
  Next i
  Sheets("Split Shipment Import").Range("C2").Resize(UBound(a)).Value = a
End Sub

 

[Edgarvelez]

Try this.

Sub Test()
  Dim a As Variant
  Dim i As Long
 
  With Sheets("Dashboard")
    a = .Range("B21", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    a(i, 1) = Format(a(i, 1), "yyyymmdd")
  Next i
  Sheets("Split Shipment Import").Range("C2").Resize(UBound(a)).Value = a
End Sub

It worked perfect! thank you very much.

 

[Peter_SSs]

You're welcome. Glad we got there in the end.