Extracting Date from string in VBA

Nygie

New Member
Joined
Apr 15, 2015
Messages
40
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,865
Office Version
2010, 2007
Platform
Windows
Hello Nygie,

May be this what you need.

Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,907
Office Version
365
Platform
Windows
To cope with UK style dates, try this mod to Leith's code
Code:
sht.Range("B5") = CLng(CDate(Right(sht.Range("A5"), 10)))
 

Nygie

New Member
Joined
Apr 15, 2015
Messages
40
Hello Nygie,

May be this what you need.

Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
31,907
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top