• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate months as 1.1/2th of a year

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

  • Chandoo Calculate Years and Months as % referenced in the table.xlsx
    9.5 KB · Views: 25
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")
 
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

upload_2019-1-24_8-5-12.png
 
Try this
 

Attachments

  • Chandoo Calculate Years and Months as % referenced in the table.xlsx
    10.5 KB · Views: 11
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
 
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!
 
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
 
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
 
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!
 
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!
 
Back
Top