number of months and days between two dates without approximation

[sluggybuig]

Hello,
I am stuck on a formula to return (in seperate cells) the number o
months and days between two dates. I've tried using DATEDIFF functio
but it's not giving me the correct number of days unless its a 30 da
month. I need it to look at the actual number of days in that month i.
28 for February.

To give some context I am working on a holiday allowance calculation fo
new employees. our holiday leave year is 1st april-31st March, 23 day
leave. If someone starts on the 22nd October, we would calculate thei
holiday allowance as (23/12)*5)) + (23/365)*10)). 5 being the number o
whole months Nov to March and 10 being the days in October between star
on the 22nd and the end of the month on the 31st.

I would like just to be able to put the start date in and the leave yea
end date and to be able to get out an allowance, without me having t
manually work out how many days there are between start date and end o
the month each time.

Any ideas?
Thanks

 

[Claus Busch]

Hello,

Am Sun, 2 Dec 2012 13:43:28 +0000 schrieb sluggybuig:

To give some context I am working on a holiday allowance calculation for
new employees. our holiday leave year is 1st april-31st March, 23 days
leave. If someone starts on the 22nd October, we would calculate their
holiday allowance as (23/12)*5)) + (23/365)*10)). 5 being the number of
whole months Nov to March and 10 being the days in October between start
on the 22nd and the end of the month on the 31st.

I would like just to be able to put the start date in and the leave year
end date and to be able to get out an allowance, without me having to
manually work out how many days there are between start date and end of
the month each time.

your start date in A1, the end date in B1:
=23/12*DATEDIF(A1,B1,"M") + 23/365*(1+DAY(EOMONTH(A1,0))-DAY(A1))


Regards
Claus Busch

 

[Ron Rosenfeld]

Hello,
I am stuck on a formula to return (in seperate cells) the number of
months and days between two dates. I've tried using DATEDIFF function
but it's not giving me the correct number of days unless its a 30 day
month. I need it to look at the actual number of days in that month i.e
28 for February.

To give some context I am working on a holiday allowance calculation for
new employees. our holiday leave year is 1st april-31st March, 23 days
leave. If someone starts on the 22nd October, we would calculate their
holiday allowance as (23/12)*5)) + (23/365)*10)). 5 being the number of
whole months Nov to March and 10 being the days in October between start
on the 22nd and the end of the month on the 31st.

I would like just to be able to put the start date in and the leave year
end date and to be able to get out an allowance, without me having to
manually work out how many days there are between start date and end of
the month each time.

Any ideas?
Thanks!

For the number of whole months from the date in A2 to the following March 31:
=DATEDIF(EOMONTH(A2-1,0),DATE(YEAR(A2)+(MONTH(A2)>3),3,31),"m")

For the number of days worked in the current month:
=EOMONTH(A2,0)-A2+1

Combining to compute the number of vacation days accrued in that first year:
=23/12*DATEDIF(EOMONTH(A2-1,0),
DATE(YEAR(A2)+(MONTH(A2)>3),3,31),"m")+
23/365*(EOMONTH(A2,0)-A2+1)

 

[Ron Rosenfeld]

For the number of whole months from the date in A2 to the following March 31:
=DATEDIF(EOMONTH(A2-1,0),DATE(YEAR(A2)+(MONTH(A2)>3),3,31),"m")

For the number of days worked in the current month:
=EOMONTH(A2,0)-A2+1

Combining to compute the number of vacation days accrued in that first year:
=23/12*DATEDIF(EOMONTH(A2-1,0),
DATE(YEAR(A2)+(MONTH(A2)>3),3,31),"m")+
23/365*(EOMONTH(A2,0)-A2+1)

Ooops. A logical error,

Number of vacation days worked in the current month should be
=EOMONTH(A2-1,0)-A2+1

And the combination for the total:

=23/12*DATEDIF(EOMONTH(A2-1,0),
DATE(YEAR(A2)+(MONTH(A2)>3),3,31),"m")+
23/365*(EOMONTH(A2-1,0)-A2+1)