Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 287
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can a service account be embedded in Workbook so that by default the macros are run with service account credentials and not the user credentials??? Divya Sanam Excel Discussion (Misc queries) 0 July 20th 06 05:15 PM
Length of Service Dom Excel Worksheet Functions 7 July 17th 06 10:47 PM
How do I find the length of time (Years & Months) between 2 dates David Picken Excel Worksheet Functions 15 March 23rd 06 08:30 PM
Convert date to length of time in months from set date MJUK Excel Worksheet Functions 1 March 19th 05 06:31 PM
how do I make different length months the same value? 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.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"