View original (French)

Calculate the number of months (with decimal) between two dates?

DelphineC -  
 DelphineC -
Hello,

I'm looking for a formula in Excel 2010 that allows me to calculate the duration of a contract by expressing the months and days as a decimal number.

For example: contract start 01/01/2014, contract end 24/04/2014 => What formula should I enter to directly obtain the elapsed time, namely: 3.8 months.

Thank you very much,
Delphine

5 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello*if you only have one decimal place, it can only be approximate, so this formula may be suitable:
    = ROUND((A1-B1+1)/365*12;1)
    which gives 3.7 with the requested dates
    if you want to count the started months, use:
    ROUNDUP(...

    best regards

    --
    To err is human, to persist is diabolical.
    5
  2. Mike-31 Posted messages 18205 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    in A1 1/01/2014
    in A2 24/04/2014

    you can use this formula
    =DATEDIF(A1;A2;"d")/30

    or
    =MOD(DATEDIF(A1;A2;"d");365)/30

    and in both cases you format the cell as a number with one decimal place, but the result and the value held by Excel will be

    3.76666666666667

    --
    A+
    Mike-31

    A period of failure is a perfect moment to sow the seeds of knowledge.
    2
  3. tontong Posted messages 2575 Registration date   Status Member Last intervention   1 064
     
    Hello everyone,
    If we do =B2- A2 in a cell formatted as Standard, we find 113 days. We may need to adjust by +1 or -1 depending on whether the limits are included or not.
    Then, 1 month is 28, 29, 30, or 31 days. To make the calculation easier, we can take a conventional value of 30, 30.5, or 30.4 days.
    Finally, to have only one decimal, we will need to round normally, up or down.
    What are your choices?
    1
  4. Gyrus Posted messages 3360 Status Member 526
     
    Hello,

    It all depends on the convention you adopt for defining the month.
    If you consider the month to be a series of 30 days, the formula is
    (end date of contract - start date of contract) / 30 and you get 3.8.

    On the other hand, if you define the month as being 1/12 of the year, the formula becomes (end date of contract - start date of contract) / (365.25/12) and you get 3.7.

    See you later!
    1
  6. DelphineC
     
    Thank you very much.
    When reading your work, it seems so much simpler! I'm not an expert and I pulled my hair out today trying to figure it out.

    I will look at each formula to find the most suitable one among all your solutions.

    Thanks again and have a great rest of the day,
    Delphine
    1