Length of service calculated in calender months.
 

Help please !

I have to calculate redundancy entitlements based on length of service
calculated in completed calender months. DATEDIF(Date1,Date2,"m")
works fine 99.9% of the time except that it ignores the days in the
dates. For this calculation an employee is deemed to have completed a
months service if the days of the month are inclusive.

For example; 22/03/2007 to 22/05/2007 is clearly two months service,
but so is 22/03/2007 to 21/05/2007 because they are deemed to have
completed the month, whereas 22/03/2007 to 20/05/2007 is only one
completed month. ( I didn't make the rule. That's how it is, )

So now I have =IF(((DAY(A10))-1)=(DAY(B10)),DATEDIF(A10,B10,"m")+1,
(DATEDIF(A10,B10,"m"))) but this doesn't work if the number of days in
the month of the second date is greater than the number of days in the
month of the first date.

I'm going crazy trying to work this out and I owe it to these people
that the entitlements are 100% accurate. Any help would be much
appreciated!

Dave

Length of service calculated in calender months.
 

Hello Dave, try


=DATEDIF(A10,B10+1,"m")

"Dave" wrote:

Help please !

I have to calculate redundancy entitlements based on length of service
calculated in completed calender months. DATEDIF(Date1,Date2,"m")
works fine 99.9% of the time except that it ignores the days in the
dates. For this calculation an employee is deemed to have completed a
months service if the days of the month are inclusive.

For example; 22/03/2007 to 22/05/2007 is clearly two months service,
but so is 22/03/2007 to 21/05/2007 because they are deemed to have
completed the month, whereas 22/03/2007 to 20/05/2007 is only one
completed month. ( I didn't make the rule. That's how it is, )

So now I have =IF(((DAY(A10))-1)=(DAY(B10)),DATEDIF(A10,B10,"m")+1,
(DATEDIF(A10,B10,"m"))) but this doesn't work if the number of days in
the month of the second date is greater than the number of days in the
month of the first date.

I'm going crazy trying to work this out and I owe it to these people
that the entitlements are 100% accurate. Any help would be much
appreciated!

Dave

Length of service calculated in calender months.
 

On 23 Feb, 00:03, daddylonglegs wrote:
Hello Dave, try

=DATEDIF(A10,B10+1,"m")



"Dave" wrote:
Help please !

I have to calculate redundancy entitlements based on length of service
calculated in completed calender months. DATEDIF(Date1,Date2,"m")
works fine 99.9% of the time except that it ignores the days in the
dates. For this calculation an employee is deemed to have completed a
months service if the days of the month are inclusive.

For example; 22/03/2007 to 22/05/2007 is clearly two months service,
but so is 22/03/2007 to 21/05/2007 because they are deemed to have
completed the month, whereas 22/03/2007 to 20/05/2007 is only one
completed month. ( I didn't make the rule. That's how it is, )

So now I have =IF(((DAY(A10))-1)=(DAY(B10)),DATEDIF(A10,B10,"m")+1,
(DATEDIF(A10,B10,"m"))) but this doesn't work if the number of days in
the month of the second date is greater than the number of days in the
month of the first date.

I'm going crazy trying to work this out and I owe it to these people
that the entitlements are 100% accurate. Any help would be much
appreciated!

Dave- Hide quoted text -

- Show quoted text -

Thanks daddylonglegs
Works fine. A biiiig help.

Dave