Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
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??? | Excel Discussion (Misc queries) | |||
Length of Service | Excel Worksheet Functions | |||
How do I find the length of time (Years & Months) between 2 dates | Excel Worksheet Functions | |||
Convert date to length of time in months from set date | Excel Worksheet Functions | |||
how do I make different length months the same value? | Charts and Charting in Excel |