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!

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sun, 2 Dec 2012 13:43:28 +0000, sluggybuig wrote:


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)

On Sun, 02 Dec 2012 19:10:15 -0500, Ron Rosenfeld wrote:

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)