Add 6 months to a date

[andyb2]

Hi Everyone,

I use a spreadsheet where I would like to add on 6 months to the date period in column A. The date period is YYYYMM. I would like to display in column C. I have attached an excel sheet as an example, showing the wrong dates (tab one) and the correct dates (tab two). I have had a go at a macro but its not very good and doesn't work.

Please see below, I would be grateful for some help.

Sub Test()
 Dim i As Long, Lr As Long, Cell As Range, Sh1 As Worksheet, K As String
 Dim L As Long
 Lr = Range("A" & Rows.Count).End(xlUp).Row
 Range("B2:B" & Lr).NumberFormat = "@"
 For i = 2 To Lr
 K = Application.WorksheetFunction.Text(Right(Range("R" & i).Value, 2) * 6, "mmmm")
 If Right(Range("R" & i).Value, 2) < 5 Then
  L = Range("A" & i).Value
  Else
  L = Range("A" & i).Value + 1
 End If
 Range("B" & i).Value = K & " " & L
 Next i
End Sub

Thanks,

Andy

 

[Marc L]

Hi,​

your result is not for 6 but 7 months, that's confusing …​

Why VBA as it can be directly achieved with a basic formula ?​

​

 

[andyb2]

sorry 7 months, I have to open a lot of these spreadsheets each day and so I would like to use a macro rather than an If statement.

 

[p45cal]

The following code puts the new dates in column B as per your sample file:

Sub Test()
With Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
  .Offset(, 1).NumberFormat = "mmm-yy"
  For Each cll In .Cells
    cll.Offset(, 1).Value = DateAdd("m", 7, DateSerial(CInt(Left(cll.Text, 4)), CInt(Right(cll.Text, 2)), 1))
  Next cll
End With
End Sub

but:

I would like to display in column C

To put them in column C change both instances of .Offset(, 1). to .Offset(, 2).

 

[Marc L]

I have to open a lot of these spreadsheets each day and so I would like to use a macro rather than an If statement.

According to your attachment a VBA demonstration allocating column C from column A directly via an Excel formula :​

Sub Demo1()
         Const F = "IF({1},EDATE(DATEVALUE(LEFT(#,4)&""/""&RIGHT(#,2)&""/1""),7))"
    With Range("C2:C" & [A1].CurrentRegion.Rows.Count)
        .NumberFormat = "mmm-yy"
        .Value2 = Evaluate(Replace(F, "#", .Columns(-1).Address))
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​