# Deduct month to number



## sbv1986 (Dec 29, 2022)

AB1202211Month number2202209Month number 2 = A1 - A23202206Month number 5 = A1 - A34202203Month number 8 = A1 - A45202112Month number 11 = A1 - A56202111Month number 12 =A1 - A67202109Month number 15 = A1 - A7 8202011Month 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 (Dec 29, 2022)

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 (Dec 29, 2022)

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 (Dec 29, 2022)

kvsrinivasamurthy said:


> 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 (Dec 29, 2022)

Or try

Book1ABC1202211Month number22022092Month number 2 = A1 - A232022065Month number 5 = A1 - A342022038Month number 8 = A1 - A4520211211Month number 11 = A1 - A5620211112Month number 12 =A1 - A6720210914Month number 15 = A1 - A7820201124Month number 24 =A1 - A8Sheet1Cell FormulasRangeFormulaB2:B8B2=DATEDIF(TEXT(A2,"0000-00-1"),TEXT($A$1,"0000-00-1"),"M")


----------



## jdellasala (Dec 29, 2022)

sbv1986 said:


> AB1202211Month number2202209Month number 2 = A1 - A23202206Month number 5 = A1 - A34202203Month number 8 = A1 - A45202112Month number 11 = A1 - A56202111Month number 12 =A1 - A67202109Month number 15 = A1 - A78202011Month number 24 =A1 - A8
> 
> Hi all
> 
> ...


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).xlsbAB120221111220221111320221111Sheet3Cell FormulasRangeFormulaB1B1=A1-202200B2B2=VALUE(RIGHT(A2,LEN(A2)-4))B3B3=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 (Dec 29, 2022)

jdellasala said:


> 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).xlsbAB120221111220221111320221111Sheet3Cell FormulasRangeFormulaB1B1=A1-202200B2B2=VALUE(RIGHT(A2,LEN(A2)-4))B3B3=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 (Dec 29, 2022)

Another way

Book1ABC1202211Month number22022092Month number 2 = A1 - A232022065Month number 5 = A1 - A342022038Month number 8 = A1 - A4520211211Month number 11 = A1 - A5620211112Month number 12 =A1 - A6720210914Month number 15 = A1 - A7820201124Month number 24 =A1 - A8Sheet1Cell FormulasRangeFormulaB2:B8B2=12*(LEFT($A$1,4) -LEFT($A2,4))+RIGHT($A$1,2)-RIGHT($A2,2)


----------



## jdellasala (Dec 29, 2022)

bferraz said:


> @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).xlsbABC1202211Month number2202209Month number 2 = A1 - A223202206Month number 5 = A1 - A354202203Month number 8 = A1 - A485202112Month number 11 = A1 - A5116202111Month number 12 =A1 - A6127202109Month number 15 = A1 - A7148202011Month number 24 =A1 - A824Sheet3Cell FormulasRangeFormulaC2:C8C2=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 (Dec 29, 2022)

bferraz said:


> Hey!
> 
> If you wanna do it with VBA:
> 
> ...


Thanks for your VBA code but code wrong if A8 = 202011, B8 must be = 24. Your code run B8 = 11


----------



## sbv1986 (Dec 29, 2022)

AB1202211Month number2202209Month number 2 = A1 - A23202206Month number 5 = A1 - A34202203Month number 8 = A1 - A45202112Month number 11 = A1 - A56202111Month number 12 =A1 - A67202109Month number 15 = A1 - A7 8202011Month 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 (Dec 30, 2022)

Phuoc said:


> Or try
> 
> Book1ABC1202211Month number22022092Month number 2 = A1 - A232022065Month number 5 = A1 - A342022038Month number 8 = A1 - A4520211211Month number 11 = A1 - A5620211112Month number 12 =A1 - A6720210914Month number 15 = A1 - A7820201124Month number 24 =A1 - A8Sheet1Cell FormulasRangeFormulaB2:B8B2=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 (Dec 30, 2022)

bferraz said:


> Hey!
> 
> If you wanna do it with VBA:
> 
> ...


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 (Dec 30, 2022)

bferraz said:


> @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 (Dec 30, 2022)

Use this:

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


----------

