Months i decimal

[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:
sepworked 19 days of 30)19/30=0,63
octworked whole month) =1
novworked 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

 

[Bernie Deitrick]

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:
sepworked 19 days of 30)19/30=0,63
octworked whole month) =1
novworked 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

 

[Daniel.M]

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:
sepworked 19 days of 30)19/30=0,63
octworked whole month) =1
novworked 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

 

[Daniel.M]

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.