Deduct month to number

[sbv1986]

	A	B
1	202211	Month number
2	202209	Month number 2 = A1 - A2
3	202206	Month number 5 = A1 - A3
4	202203	Month number 8 = A1 - A4
5	202112	Month number 11 = A1 - A5
6	202111	Month number 12 =A1 - A6
7	202109	Month number 15 = A1 - A7
8	202011	Month number 24 =A1 - A8

Hi all

I have data like table above, Column (A) is number format as: YYYYMM

Now I want A1 deduct each row belove (A2:A8) then fill Result in Column (B)
For example:
A1 - A2, then B2 must be 2 months
A1 - A5, B5 = 11
A1 - A8, B2 = 24

Please help me to dot his by VBA exel, many thanks./.

 

[sbv1986]

Or try

Book1
	A	B	C
1	202211		Month number
2	202209	2	Month number 2 = A1 - A2
3	202206	5	Month number 5 = A1 - A3
4	202203	8	Month number 8 = A1 - A4
5	202112	11	Month number 11 = A1 - A5
6	202111	12	Month number 12 =A1 - A6
7	202109	14	Month number 15 = A1 - A7
8	202011	24	Month number 24 =A1 - A8
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=DATEDIF(TEXT(A2,"0000-00-1"),TEXT($A$1,"0000-00-1"),"M")

YOur formula work well, can you convert this to VBA code in excel?

 

[sbv1986]

Hey!

If you wanna do it with VBA:

Sub DeductMonth()

Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet

With ws

Row = .Range("A1048576").End(xlUp).Row

date1 = CDate(Left(.Range("A1").Value, 4) & "-" & Right(.Range("A1").Value, 2))
.Range("B1").Value = Right(.Range("A1").Value, 2)

For i = 2 To Row

date2 = CDate(Left(.Range("A" & i).Value, 4) & "-" & Right(.Range("A" & i).Value, 2))

    .Range("B" & i).Value = DateDiff("m", date2, date1)

Next i

End With

End Sub

Thanks for your code.

I Put small code then run your code work well

"Dim Row, i As Long, date1 As String, date2 As String"

 

[kvsrinivasamurthy]

@kvsrinivasamurthy

Your formula is not taking in consideration the variations from years!

Pl check once again . First portion is for years and second portion for months.
Formula edited.
=12*(Value(Left($A$1,4)) -Value(Left($A2,4)))+Value(right($A$1,2))-value(right($A2,2))

 

[Tom.Jones]

Use this:

=(LEFT($A$1,4)-LEFT($A2,4))*12+RIGHT($A$1,2)-RIGHT($A2,2)