Convert to Date "dd-mm-yyyy"

[vmjan02]

I have a row from H8 to BC8 in this format, is there aw way vba code to convert it to dd-mm-yyyy

24/06/22, 00:00

24/06/22, 01:00

24/06/22, 02:00

24/06/22, 03:00

24/06/22, 04:00

24/06/22, 05:00

 

[Akuini]

This is what i was looking for, thanks a ton Akuini
thanks a lot Alex your formula is also perfect, but needed a vba code.

Glad it work, but I specified the range incorrectly, so you need to amend it to suit:

For Each c In Range("H8:M8")  '<--amend to suit

 

[vmjan02]

Glad it work, but I specified the range incorrectly, so you need to amend it to suit:

For Each c In Range("H8:M8")  '<--amend to suit

yes that i did.

 

[lighting77]

I was researching and I think Power query could help as well . Have fun

 

[vmjan02]

Another option, with vba:
This will remove the time part, and keep the date only.

Sub TO_DATE1()
Dim c As Range
For Each c In Range("H8:M8")
    If Len(c) > 7 Then c = CDate(Left(c, 8))
Next
Range("H8:BC8").NumberFormat = "dd-mm-yyyy"
End Sub

Hi Akuini

this is having a problem as now the date is also coming like this

1/07/22, 00:00

1/07/22, 01:00

1/07/22, 02:00

1/07/22, 03:00

and like this as well

24/06/22, 00:00

24/06/22, 01:00

24/06/22, 02:00

24/06/22, 03:00

24/06/22, 04:00

24/06/22, 05:00

[QUOTE]
If Len(c) > 7 Then c = CDate(Left(c, 8))  'Run time error Type mismatch
[/QUOTE]

 

[Akuini]

Ok, try this one:

Sub TO_DATE2()
Dim c As Range, tx As String
For Each c In Range("H8:BC8")
    tx = c.Value
    If Mid(tx, 2, 1) = "/" Then tx = "0" & tx
    If Len(tx) > 7 Then c = CDate(Left(tx, 8))
Next
Range("H8:BC8").NumberFormat = "dd-mm-yyyy"
End Sub

 

[vmjan02]

Ok, try this one:

Sub TO_DATE2()
Dim c As Range, tx As String
For Each c In Range("H8:BC8")
    tx = c.Value
    If Mid(tx, 2, 1) = "/" Then tx = "0" & tx
    If Len(tx) > 7 Then c = CDate(Left(tx, 8))
Next
Range("H8:BC8").NumberFormat = "dd-mm-yyyy"
End Sub

thanks a ton man, you are a life saver..

 

[Akuini]

You're welcome.