Calculate the number of months (with decimal) between two dates?
DelphineC
-
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
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 réponses
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.
= 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.
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.
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.
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?
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?
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!
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!