Change date values and retain format

Question

I want to replace a repeating year of dates in a column.

I started with a nested for loop in a single module, then I changed the code to be used for each sheet in it's relative module.

I tried replacing the repeating year with nothing and then adding the right year but it hangs at the middle.

Sub change_dates()

Dim wb As Workbook
Dim o As Long
Dim k As Long
Dim y As Long

Set wb = ThisWorkbook

y = Year(Date)
o = 2
k = wb.Worksheets(11).Cells(2, 10).Value + 2

Do While o < k
    If Mid(wb.Worksheets(11).Cells(o, 1), 4, 2) = 12 Then
        y = y - 1
    End If
    wb.Worksheets(11).Cells(o, 1) = Left(wb.Worksheets(11).Cells(o, 1), 6) & CStr(y)
    o = o + 1
Loop

End Sub

The cell values after I run the code:

The first part is how I want it. In the second part the month is changing and the formatting too.

The original values of the column.

...
22-01-2022
21-12-2022
21-11-2022
21-10-2022
21-09-2022
21-08-2022
21-07-2022
21-06-2022
21-05-2022
21-04-2022
21-03-2022
21-02-2022
21-01-2022
20-12-2022
20-11-2022
20-10-2022
20-09-2022
20-08-2022
20-07-2022
20-06-2022
...

This is the desired result:

Further clarifications:

I get date as a result of =A2+1.
The A column cells are all formatted dd-mm-yyyy
k is returning the right number.
The result should be, all the days and months remaining the same, and the repeating year 2022 changed to a year that starts in 2022 and decreases by one starting from the top down every time it's December.

user_3219613 · Answer

The issue is if you use text/string functions like Mid() or Left() you change from a real numeric date (you can actually calculate with) to a text that only looks like a date but is just text (you cannot calculate with that anymore). And Excel does not know that this is a date.

So whenever working with dates use numeric date functions like Day(), Month() and Year() to split the date into parts and use DateSerial(y, m, d) to put a new date together. This will create a real numeric date you can calculate with, and that you can format with .NumberFormat.

I changed your Do loop into a For loop that auto increments o on Next o, this looks a bit cleaner.

Public Sub change_dates()
    Dim ws As Worksheet  ' define your worksheet only once
    Set ws = ThisWorkbook.Worksheets(11) ' if it ever changes from 11 to 12 it only needs to be changed here
    
    Dim y As Long
    y = Year(Date)
    
    Dim k As Long
    k = ws.Cells(2, 10).Value + 1
    
    Dim o As Long
    For o = 2 To k  ' loop from 2 to k
        Dim m As Long  ' get month of current cell
        m = Month(ws.Cells(o, 1))
        
        Dim d As Long  ' get day of current cell
        d = Day(ws.Cells(o, 1))
        
        If m = 12 Then  ' check if year needs to change
            y = y - 1
        End If
        
        ws.Cells(o, 1) = DateSerial(y, m, d)  ' create a real numeric date and write it to the cell
        
        ' if the date needs to show in another format just change the numberformat to whatever you need
        'ws.Cells(o, 1).NumberFormat = "dd-mm-yyyy"
    Next o
End Sub

user_9814069 · Answer

Manipulate Dates in Column

Option Explicit

Sub change_dates()
    
    Const wsID As Variant = 11 ' e.g. "Sheet11" is a little more reliable
    Const fRow As Long = 2
    Const Col As String = "A"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the column range ('rg').
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsID)
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data
    Dim rg As Range: Set rg = ws.Range(ws.Cells(fRow, Col), ws.Cells(lRow, Col))
    
    Dim cCell As Range
    Dim cValue As Variant
    Dim y As Long, m As Long, d As Long
    Dim FirstDone As Boolean
    
    ' Read and write.
    For Each cCell In rg.Cells
        cValue = cCell.Value
        If IsDate(cValue) Then
            If FirstDone Then
                d = Day(cValue)
                m = m - 1
                If m = 0 Then
                    m = 12
                    y = y - 1
                End If
                cCell.Value = DateSerial(y, m, d)
            Else ' first date stays as is
                y = Year(cValue)
                m = Month(cValue)
                FirstDone = True
            End If
        End If
        
    Next cCell
    
End Sub

Change date values and retain format

2 Answers

Manipulate Dates in Column