VBA - Dates

[ChrisFoster]

I have an excel document where in column H is a list of dates, I am not concerned with the year though so want to change the date in each cell to the current year.
So for example I have a date of 20/05/2012, I want that to change to 20/05/2024. I am in the UK hence why using UK date format.

I can manually use Text to Columns to do this but isn't the best way when using VBA.

What is the best VBA code to do this please?

 

[awoohaw]

Do you need VBA?

Is it text as a date or is it really a date?
use this if it is text:

(you could get an error with this if the data is copied and you have strange text characters after the year.)

=Date(textafter(H2,"/",2),textbefore(textafter(H2,"/"),"/"),left(H2,2))

use this if H is really a date:

=Date(2024,Month(H2),Day(H3))

 

[Joe4]

Assuming it is entered as a valid date on your sheet, here is a simple example to show how you can pull the date from cell H1 and convert it to the current year dynamically.

Sub DateUpdate()

    Dim dt1 As Date
    Dim dt2 As Date
    
'   Get date from cell H1
    dt1 = Range("H1")
    
'   Convert date to current year
    dt2 = DateSerial(Year(Date), Month(dt1), Day(dt1))
    
'   Display new date
    MsgBox dt2
    
End Sub

 

[ChrisFoster]

Assuming it is entered as a valid date on your sheet, here is a simple example to show how you can pull the date from cell H1 and convert it to the current year dynamically.

Sub DateUpdate()

    Dim dt1 As Date
    Dim dt2 As Date
  
'   Get date from cell H1
    dt1 = Range("H1")
  
'   Convert date to current year
    dt2 = DateSerial(Year(Date), Month(dt1), Day(dt1))
  
'   Display new date
    MsgBox dt2
  
End Sub

Thanks.
But I get a debug error at

' Get date from cell H1
dt1 = Range("H1")

 

[ChrisFoster]

Do you need VBA?

Is it text as a date or is it really a date?
use this if it is text:

(you could get an error with this if the data is copied and you have strange text characters after the year.)

=Date(textafter(H2,"/",2),textbefore(textafter(H2,"/"),"/"),left(H2,2))

use this if H is really a date:

=Date(2024,Month(H2),Day(H3))

It is VBA code I need for this mate, not a formula.

 

[Joe4]

Thanks.
But I get a debug error at

' Get date from cell H1
dt1 = Range("H1")

Do you have a valid date in cell H1?

If you enter this formula anywhere in a blank cell, what does it return?

=ISNUMBER(H1)

 

[ChrisFoster]

Do you have a valid date in cell H1?

If you enter this formula anywhere in a blank cell, what does it return?

=ISNUMBER(H1)

Ahh. H1 has the title of date. The dates then run from H2 onwards dynamically.
When using =ISNUMBER(H2) I get a TRUE result.

 

[Joe4]

OK, so if you substitute H2 for H1 in that code, it should work.

 

[Joe4]

If you want it to update all the rows from row 2 to the last row with data in column H, then as long as every one of those rows has a valid date, this code will update all of them at once.

Sub DateUpdate()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column H
    lr = Cells(Rows.Count, "H").End(xlUp).Row
   
'   Loop through all rows starting with row 2
    For r = 2 To lr
'       Update to current year
        Cells(r, "H").Value = DateSerial(Year(Date), Month(Cells(r, "H")), Day(Cells(r, "H")))
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

 

[ChrisFoster]

If you want it to update all the rows from row 2 to the last row with data in column H, then as long as every one of those rows has a valid date, this code will update all of them at once.

Sub DateUpdate()

    Dim lr As Long
    Dim r As Long
  
    Application.ScreenUpdating = False
  
'   Find last row with data in column H
    lr = Cells(Rows.Count, "H").End(xlUp).Row
  
'   Loop through all rows starting with row 2
    For r = 2 To lr
'       Update to current year
        Cells(r, "H").Value = DateSerial(Year(Date), Month(Cells(r, "H")), Day(Cells(r, "H")))
    Next r
  
    Application.ScreenUpdating = True
  
End Sub

Genius. Thanks so much, that's worked perfectly.