Months i decimal

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

How do I do to get av 2 decimal value of a month?

For ex.: I have been employed between 12-Sep and 16-Nov.
This is 2 "broken" months and 1 whole month. I i make a
calculating of this It´s ending up beeing 2,16 months, by
using this calculating:
sep:(worked 19 days of 30)19/30=0,63
oct:(worked whole month) =1
nov:(worked 16 days of 30)16/30=0,53
Summary 0,63 + 1 + 0,53=2,16

How can i make a formula in Excel with this calculating?
/Peter
 
Peter,

With your first date in A1, and your second in B1, use this formula
(all on one line, so watch line wrapping when you enter it into your
cell):

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-1+(DATE(YEAR(A1),MONTH(A1)
+1,1)-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0))+DAY(B1)/DAY(DATE(YEAR(B1),
MONTH(B1)+1,0))

This will account for differing years as well.

Note that for your example, it will return 2.17, since the parts are
1, 0.5333333, and 0.6333333, for a total of 2.166666. If you want,
you can add rounding to change this behavior: this formula will return
2.16.

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)-1+ROUND((DATE(YEAR(A1),MON
TH(A1)+1,1)-A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,0)),2)+ROUND(DAY(B1)/DAY
(DATE(YEAR(B1),MONTH(B1)+1,0)),2)

HTH,
Bernie
MS Excel MVP

How do I do to get av 2 decimal value of a month?

For ex.: I have been employed between 12-Sep and 16-Nov.
This is 2 "broken" months and 1 whole month. I i make a
calculating of this It´s ending up beeing 2,16 months, by
using this calculating:
sep:(worked 19 days of 30)19/30=0,63
oct:(worked whole month) =1
nov:(worked 16 days of 30)16/30=0,53
Summary 0,63 + 1 + 0,53=2,16

How can i make a formula in Excel with this calculating?
/Peter
 
Hi Peter,

With your first date in A1 and your second date in B1:

=1-(DAY(A1)-1)/(32-DAY(A1-DAY(A1)+32))+DAY(B1)/(32-DAY(B1-DAY(B1)+32))
+DATEDIF(,B1,"m")-DATEDIF(,A1,"m")-1

Regards,

Daniel M.

How do I do to get av 2 decimal value of a month?

For ex.: I have been employed between 12-Sep and 16-Nov.
This is 2 "broken" months and 1 whole month. I i make a
calculating of this It´s ending up beeing 2,16 months, by
using this calculating:
sep:(worked 19 days of 30)19/30=0,63
oct:(worked whole month) =1
nov:(worked 16 days of 30)16/30=0,53
Summary 0,63 + 1 + 0,53=2,16

How can i make a formula in Excel with this calculating?
/Peter
 
Eliminating the +1 and -1:

=DAY(B1)/(32-DAY(B1-DAY(B1)+32))-(DAY(A1)-1)/(32-DAY(A1-DAY(A1)+32))+
DATEDIF(,B1,"m")-DATEDIF(,A1,"m")

Regards,

Daniel M.
 
Back
Top