Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 4
Length of service calculated in calender months.

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

#2
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 287
Length of service calculated in calender months.

Hello Dave, try

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

"Dave" wrote:

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

#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 4
Length of service calculated in calender months.

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

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

"Dave" wrote:

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 -

Works fine. A biiiig help.

Dave

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Divya Sanam Excel Discussion (Misc queries) 0 July 20th 06 05:15 PM Dom Excel Worksheet Functions 7 July 17th 06 10:47 PM David Picken Excel Worksheet Functions 15 March 23rd 06 08:30 PM MJUK Excel Worksheet Functions 1 March 19th 05 06:31 PM bee333 Charts and Charting in Excel 1 February 10th 05 03:21 AM

All times are GMT +1. The time now is 07:51 AM.