1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Kelli Webb, Jan 23, 2019.

  1. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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

    Attached Files:

  2. azumi

    azumi Active Member

    Messages:
    226
    Maybe, put this on G3 and copied down then formatted as percentage:

    =MONTH(E3)/12
  3. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,727
    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")
  5. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    Hi Hui, I can't get this to work. Did it work as per my example ie. column F?
  6. azumi

    azumi Active Member

    Messages:
    226
    Change to

    =VALUE(MONTH(E3)/12)
  7. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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
  8. azumi

    azumi Active Member

    Messages:
    226
    Try this

    Attached Files:

  9. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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
  10. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,727
    F3: DATEDIF($D$3,$E$3,"y")+(MONTH(E3)-MONTH(D3))/12
    Copy down
  11. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    512
    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!
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,727
    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
  13. Kelli Webb

    Kelli Webb Member

    Messages:
    49
  14. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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
  15. azumi

    azumi Active Member

    Messages:
    226
    Hi @Kelli you're already have the formula that works, but why still want another one?
    =YEARFRAC(D3;E3;1)
  16. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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
  17. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    512
    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!
    Thomas Kuriakose and ExcelSur like this.
  18. Kelli Webb

    Kelli Webb Member

    Messages:
    49
  19. Kelli Webb

    Kelli Webb Member

    Messages:
    49
    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!


Share This Page