Extracting Date from string in VBA

[Nygie]

Hi Everyone

Having searched a number of threads and not found the answer I am looking for I thought I would again call on your collective expertise to give me a shove in the right direction.

What I am trying to achieve is from the cell A5 which = Week ending 06/09/2019 I am extracting the date using RIGHT. My code extracts the date to the adjacent cell and formats the cell to UK date. To here it looks OK.
Excel 2007
I then copy the date into a range of cells. This is where it all goes pear shaped. The date where the day is greater than 12 show correctly as ddmmyyyy but if the day is less than 12 it shows as mmddyyyy.
I tried using DATE(RIGHT) to extract the date but it wouldn't work.
Any pointers would be greatly appreciated.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] With sht
LR = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Range("B5").NumberFormat = "dd/mm/yyyy"
sht.Range("B5").Value = "=RIGHT(A5,10)"
'sht.Range("B5").Formula = "=DATE(RIGHT(A5,4),MID(A5,7,2),MID(A5,10,2)"
sht.Range("J10:J" & LR).Value = sht.Range("B5").Value
sht.Range("B7").NumberFormat = "dd/mm/yyyy"
'sht.Range("B7").Value = "=RIGHT(A7,10)"
sht.Range("K10:K" & LR).Value = sht.Range("B7").Value


End With[/FONT]

 

[Leith Ross]

Hello Nygie,

May be this what you need.

    Dim lr  As Long
    Dim Rng As Range
    Dim sht As Worksheet
    
        Set sht = Worksheets("Sheet1")
        
        sht.Range("B5") = Right(sht.Range("A5"), 10)
        sht.Range("B5").NumberFormat = "dd/mm/yyy"
        
        lr = sht.Cells(Rows.Count, "A").End(xlUp).Row
        
        Set Rng = sht.Range("J10:J" & lr)
            Rng.Value = sht.Range("B5")
            Rng.NumberFormat = "dd/mm/yyy"

 

[Fluff]

To cope with UK style dates, try this mod to Leith's code

sht.Range("B5") = CLng(CDate(Right(sht.Range("A5"), 10)))

 

[Nygie]

To cope with UK style dates, try this mod to Leith's code

sht.Range("B5") = CLng(CDate(Right(sht.Range("A5"), 10)))

You Sir are a genious.
Thank you, worked brilliantly.

 

[Nygie]

Hello Nygie,

May be this what you need.

    Dim lr  As Long
    Dim Rng As Range
    Dim sht As Worksheet
    
        Set sht = Worksheets("Sheet1")
        
        sht.Range("B5") = Right(sht.Range("A5"), 10)
        sht.Range("B5").NumberFormat = "dd/mm/yyy"
        
        lr = sht.Cells(Rows.Count, "A").End(xlUp).Row
        
        Set Rng = sht.Range("J10:J" & lr)
            Rng.Value = sht.Range("B5")
            Rng.NumberFormat = "dd/mm/yyy"

Thank you Leith

 

[Fluff]

Glad we could help & thanks for the feedback