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./.

 

[kvsrinivasamurthy]

In B2 , copy down

=12*(Value(Left($A$1,4)) -Value(Left($A$1,4)))+Value(right($A$1,2))-value(right($A2,2))

 

[bferraz]

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

 

[bferraz]

In B2 , copy down

=12*(Value(Left($A$1,4)) -Value(Left($A$1,4)))+Value(right($A$1,2))-value(right($A2,2))

@kvsrinivasamurthy

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

 

[Phuoc]

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")

 

[jdellasala]

	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./.

When you say that Column A is "number format as: YYYYMM", it is unclear if 202211 is actually a number, or if it's just text, or if it's any date in November 2022 such as 11/1/2022. They are three COMPLETELY different things, and to get the month number from each would be completely different:

Book1 (version 2).xlsb
	A	B
1	202211	11
2	202211	11
3	202211	11
Sheet3

Cell Formulas
Range	Formula
B1	B1	=A1-202200
B2	B2	=VALUE(RIGHT(A2,LEN(A2)-4))
B3	B3	=MONTH(A3)

Note that XL2BB is not preserving the Right/Left justification of Column A. Rows 1 and 3 are numbers and therefore Right justified automatically without any formatting applied manually, and row 2 is Text and therefore Left justified automatically without any formatting applied manually. With the exception of final reports, it's always best to NOT change any horizontal formatting since it's an easily spotted clue to possible problems. As long as I'm talking about formatting, NEVER use Merge & Center - it TOTALLY screws up a Worksheet's structure. Instead use Center Across Selection in the Horizontal Alignment format section.
p.s. I have no idea why VBA is even considered for this problem, nor do I comprehend what "not taking in consideration the variations from years" means or has to do with it.

 

[bferraz]

When you say that Column A is "number format as: YYYYMM", it is unclear if 202211 is actually a number, or if it's just text, or if it's any date in November 2022 such as 11/1/2022. They are three COMPLETELY different things, and to get the month number from each would be completely different:

Book1 (version 2).xlsb
	A	B
1	202211	11
2	202211	11
3	202211	11
Sheet3

Cell Formulas
Range	Formula
B1	B1	=A1-202200
B2	B2	=VALUE(RIGHT(A2,LEN(A2)-4))
B3	B3	=MONTH(A3)

Note that XL2BB is not preserving the Right/Left justification of Column A. Rows 1 and 3 are numbers and therefore Right justified automatically without any formatting applied manually, and row 2 is Text and therefore Left justified automatically without any formatting applied manually. With the exception of final reports, it's always best to NOT change any horizontal formatting since it's an easily spotted clue to possible problems. As long as I'm talking about formatting, NEVER use Merge & Center - it TOTALLY screws up a Worksheet's structure. Instead use Center Across Selection in the Horizontal Alignment format section.
p.s. I have no idea why VBA is even considered for this problem, nor do I comprehend what "not taking in consideration the variations from years" means or has to do with it.

@jdellasala Sorry if it wasn't clear but English is not my main language.
The code I provided was in VBA per request from @sbv1986.
What I mentioned about "not taking in consideration the variations from years" was that he has data from multiple years on this table as the formula provided wouldn't work for that since it was looking only for the months and not years. If that was also unclear please let me know so I can better explain!

 

[Phuoc]

Another way

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	=12*(LEFT($A$1,4) -LEFT($A2,4))+RIGHT($A$1,2)-RIGHT($A2,2)

 

[jdellasala]

@jdellasala Sorry if it wasn't clear but English is not my main language.
The code I provided was in VBA per request from @sbv1986.
What I mentioned about "not taking in consideration the variations from years" was that he has data from multiple years on this table as the formula provided wouldn't work for that since it was looking only for the months and not years. If that was also unclear please let me know so I can better explain!

What I said about VBA was in general. The problem doesn't require VBA, and there are MUCH easier ways to solve the problem using simple formulas.
As for the years thing, I was wrong. I now see that he's looking for total months. My apologies.

That said, and in hopes that the data is actually a date, the little known DATEDIF comes to the rescue!

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

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2>$A$1,DATEDIF($A$1,A2,"M"),DATEDIF(A2,$A$1,"M"))

It's not well supported in Excel, but it is an official function and does EXACTLY what you're looking for - as long as you use actual dates!
DATEDIF
The only reason for the IF clause above is because DATEDIF's parameters are start_date and end_date and throws an error if the first parameter is larger than the second. If you know that the top date (A1) will always be larger than the rest of the dates, the formula

=DATEDIF(A2,$A$1,"M")

will work fine.

 

[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 VBA code but code wrong if A8 = 202011, B8 must be = 24. Your code run B8 = 11