# Calculate months as 1.1/2th of a year

#### Kelli Webb

##### Member
Hi, I would like to find a formula to calculate the years and months, as per the table provided below

Months %
1 month 0.08
2 months 0.17
3 months 0.25
4 months 0.33
5 months 0.42
6 months 0.50
7 months 0.58
8 months 0.66
9 months 0.75
10 months 0.83
11 months 0.92
12 months 1.00

thanks

#### Attachments

• 9.5 KB Views: 25

#### azumi

##### Active Member
Maybe, put this on G3 and copied down then formatted as percentage:

=MONTH(E3)/12

#### Kelli Webb

##### Member
Maybe, put this on G3 and copied down then formatted as percentage:

=MONTH(E3)/12
Hi Azumi, Works perfectly to calculate the month. What would it be if it has to calculate the year and the year and month as 1.1/2th?
Kelli

#### Hui

##### Excel Ninja
Staff member
If you have a list of integers starting in A2 and going down the worksheet
Apply this formula
=(INT(A2/12)+1)&TEXT(A2/12-INT(A2/12),".00")

#### Kelli Webb

##### Member
If you have a list of integers starting in A2 and going down the worksheet
Apply this formula
=(INT(A2/12)+1)&TEXT(A2/12-INT(A2/12),".00")
Hi Hui, I can't get this to work. Did it work as per my example ie. column F?

#### azumi

##### Active Member
Change to

=VALUE(MONTH(E3)/12)

#### Kelli Webb

##### Member
Change to

=VALUE(MONTH(E3)/12)
Hi Azumi, I'm still not getting the desired results i.e. in column F.

The calculation is the years and months between the start and end dates that I require, displaying the months as per the table i.e. 0.0833 for each month

Try this

#### Attachments

• 10.5 KB Views: 11

#### Kelli Webb

##### Member
Hi Azumi, Something must be getting lost in communication. The end result should look like I have posted above i.e. row 3 should equal 10.08 (10 years 1 month). Thank you so much for your assistance, I really do appreciate it.
Kelli

#### Hui

##### Excel Ninja
Staff member
F3: DATEDIF(\$D\$3,\$E\$3,"y")+(MONTH(E3)-MONTH(D3))/12
Copy down

#### John Jairo V

##### Well-Known Member
Hi, Kelly!

I'm lost here. Helps a lot if you put manually the results that you want in your excel, and explain how it works. Help us to help you! Blessings!

#### Hui

##### Excel Ninja
Staff member
The questions has also morphed from what was originally asked ?

Please be specific in questions and use real scenario's that replicate the problem in future posts

#### Kelli Webb

##### Member
Hi, Kelly!

I'm lost here. Helps a lot if you put manually the results that you want in your excel, and explain how it works. Help us to help you! Blessings!

#### Kelli Webb

##### Member
Hi Hui, My expected results are in column F under S/be.

I'm after the years and months between column D & E, but the months to be as per the table on the left.

thanks, Kelli

#### azumi

##### Active Member
Hi @Kelli you're already have the formula that works, but why still want another one?
=YEARFRAC(D3;E3;1)

#### Kelli Webb

##### Member
Hi @Kelli you're already have the formula that works, but why still want another one?
=YEARFRAC(D3;E3;1)
Hi Azumi, It isn't exactly the same as the table for a couple of them, and I was hoping for something that gave the correct result.

Kelli

#### John Jairo V

##### Well-Known Member
Hi, to all!

@Kelli Webb. Is this what you want?

=DATEDIF(D3-1,E3,"y")+IFERROR(VLOOKUP(TEXT(DATEDIF(D3-1,E3,"ym"),"[=1]0 ""Month"";0 ""Months"""),A\$3:B\$14,2,),)

Or this:

=DATEDIF(D3-1,E3,"y")+ROUND(DATEDIF(D3-1,E3,"ym")/12,2)

Both formulas give same answers. Blessings!

#### Kelli Webb

##### Member
Hi, to all!

@Kelli Webb. Is this what you want?

=DATEDIF(D3-1,E3,"y")+IFERROR(VLOOKUP(TEXT(DATEDIF(D3-1,E3,"ym"),"[=1]0 ""Month"";0 ""Months"""),A\$3:B\$14,2,),)

Or this:

=DATEDIF(D3-1,E3,"y")+ROUND(DATEDIF(D3-1,E3,"ym")/12,2)

Both formulas give same answers. Blessings!

#### Kelli Webb

##### Member
Hi John,

Thank you so much, this is exactly what I was looking for - just perfect.

Sorry for the late reply but I have only just had time to reflect on my emails etc. This will make my future reporting effortless.

Cheers!

Hi, to all!

@Kelli Webb. Is this what you want?

=DATEDIF(D3-1,E3,"y")+IFERROR(VLOOKUP(TEXT(DATEDIF(D3-1,E3,"ym"),"[=1]0 ""Month"";0 ""Months"""),A\$3:B\$14,2,),)

Or this:

=DATEDIF(D3-1,E3,"y")+ROUND(DATEDIF(D3-1,E3,"ym")/12,2)

Both formulas give same answers. Blessings!