If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
CALCULATE FUTURE DATES
For example, the termination date is in A1, the number of severance days is
in B1. Use the following formula in C1: =A1+B1 You may have to format the result into date format, but that should do the trick. -- Michael J. Malinsky "Lynn" wrote in message ... I need to calculate benefit and severance end dates from a given employment termination date and do not know how to do it. For example: Termination Date: February 13. Employees receive 2 weeks severance/benefits per year of service, the weeks range anywhere from 2 to 50. I do not know how to enter a formula to calculate the severance/benefit end date. Thanks for any assistance you can provide. |
#2
|
|||
|
|||
CALCULATE FUTURE DATES
Lynn,
Here's a few formulas for you. Assumes start date in A1 and end date in B1 Years of service rounded down to nearest year: =ROUNDDOWN(YEARFRAC(A1,B1,1),0) Years of service rounded down to nearest half year: =ROUNDDOWN(YEARFRAC(A1,B1,1)*2,0)/2 Add 2 weeks per year of service. (years of service in C1) =B1+(C1*14) You can combine the formulas if you prefer. Post in this thread if you need any help. Good Luck, Mark Graesser ----- Lynn wrote: ----- I need to calculate benefit and severance end dates from a given employment termination date and do not know how to do it. For example: Termination Date: February 13. Employees receive 2 weeks severance/benefits per year of service, the weeks range anywhere from 2 to 50. I do not know how to enter a formula to calculate the severance/benefit end date. Thanks for any assistance you can provide. |
#4
|
|||
|
|||
CALCULATE FUTURE DATES
Hi Mark!
YEARFRAC is definitely in error with calculations of more than a year. Myrna Larson and I played around with this some time ago. On the way through we discovered that there were errors with DATEDIF as well where the "yd" argument is used. In the case of YEARFRAC the main problem was the peculiar algorithm for determining the number of days in a year; they take the average number of days in the years spanned by the starting and ending dates. It could be said that this is not an error but a question of definition although independently Myrna an I both came up with an approach that fits in with most people's logic. In the case of DATEDIF and the "yd" argument it's a plain old stuff up. Here's the gory details: Analysis ToolPak has a YEARFRAC function which has apparent attractions for calculating the difference between dates in terms of a year and fractions of a year. The difficulty of such calculations is that years have varying lengths and approximations of 365.25 produce errors because 365.25 may not be the average length of year involving any two dates. YEARFRAC has the syntax: =YEARFRAC(start_date,end_date,basis) YEARFRAC has alternative third arguments for its calculations. Outside specialist financial instrument basis arguments 2 and 4, the two that are of initial interest are Argument basis = 1 (Actual / Actual) and Argument basis = 3 (Actual / 365). The numerator of these fractions is the number of days and the denominator is the assumed length of year. Where YEARFRAC basis = 1 and the dates are more than a year apart it can be shown that YEARFRAC uses as its divisor the average number of days in the years start_date to end_date inclusive of the start_date and end_date years irrespective of where in those years the start_date and end_date falls. But where the dates are 1 year or less apart it uses either 365 or 366 depending upon whether either year is a Leap Year and where in the year the start_date and end_date fall relative to 29-Feb. That makes YEARFRAC basis 1 difficult to replicate as a formula and it produces non-exact years of service for all anniversary dates except the first. I can't see how YEARFRAC can be used for years of service with basis 1 which strikes me as the most likely candidate. YEARFRAC with basis = 3 similarly fails to produce exact years for all anniversary dates except the first. This is important in the context of finding a method that can be used for legal entitlements and any alternative must have consistency. The method must also be one that is capable of being translated to an acceptable definition in terms of how it is calculated or lawyers will have a ball and we will be that ball. (Not a bad mixed metaphor that one!) So I go for a formula approach and hope that I can get a clear up definition on the way. Calculating whole years is easy: =DATEDIF(A1,B1,"y") I've tested this and it always seems to give the right answer and it's the simplest approach. Calculating residual days is not so easy! =DATEDIF(A1,B1,"yd") returns some annoying 1 day errors for example: =DATEDIF("10-Apr-2003","9-Mar-2005","yd") returns: 333 =DATEDIF("10-Apr-2003","10-Mar-2005","yd") returns: 335 There's got to be something wrong there! Also: =DATEDIF("15-Sep-2002","14-Mar-2004","yd") returns: 181 =DATEDIF("15-Sep-2002","15-Mar-2004","yd") returns: 181 There's got to be something wrong there as well! So to calculate the days since the last anniversary date I use: =B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1) ) I've tested this and it doesn't appear to produce any errors. So we have an algorithm for years and days between dates that yields the correct answer: =DATEDIF(A1,B1,"y") & " y " & B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1) ) & " d" This in itself is a very useful formula as it is different from the one that we usually use and which produces errors for dates such as those used above. Determining the numerator for calculating the fraction of a year for the residual days is a debatable point. Various arguments can be put forward for different approaches but to my mind the most logical is to use the count of days between the last anniversary and the next anniversary. In essence I'm saying that we ask the question, "How many days are there between the last anniversary and the next anniversary and what decimal fraction of those days have I used up?" Since my formula and your UDF produce the same answers, it appears that we are using the same logic (but not necessarily the correct one vbg). To calculate this number of days, I need to calculate the date of the next anniversary and deduct the date of the previous anniversary and here, of course I hit the same problem of potential for error in calculating days. Date of Next Anniversary is: =DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY( A1)) Date of Previous Anniversary is: =DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1 )) So days between Anniversaries is: =DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY( A1))-DATE(YEAR(A1)+D ATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) So I can now calculate the fraction of the year between anniversaries. =(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1) ))/(DATE(YEAR(A 1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+DATEDIF(A1,B1 ,"y"),MONTH(A1),DAY(A1))) Add that to the number of whole years: =DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY (A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY( A1))-DATE(YEA R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))) This algorithm was tested against a VBA function solution by Myrna Larson and they checked with one another in all cases. The VBA solution is: Function YearDiff(ByVal StartDate As Date, _ Optional ByVal EndDate As Date = #1/1/100#) As Double 'modified 02/01/2003 Dim AnnDay As Long Dim AnnMonth As Long Dim AnnYear As Long Dim ltemp As Date Dim NextAnn As Date Dim PrevAnn As Date If EndDate = #1/1/100# Then EndDate = Date 'put in right order if necessary If StartDate EndDate Then ltemp = StartDate StartDate = EndDate EndDate = ltemp End If 'get anniversary date in ending year AnnYear = Year(EndDate) AnnMonth = Month(StartDate) AnnDay = Day(StartDate) 'assume it's already occurred PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay) If PrevAnn = EndDate Then 'assumption that it's past was correct 'next anniversary is 1 year in the future NextAnn = DateSerial(AnnYear + 1, AnnMonth, AnnDay) Else 'wrong -- we calculated the *next* anniversary NextAnn = PrevAnn AnnYear = AnnYear - 1 PrevAnn = DateSerial(AnnYear, AnnMonth, AnnDay) End If YearDiff = AnnYear - Year(StartDate) + _ (EndDate - PrevAnn) / (NextAnn - PrevAnn) End Function 'YearDiff And here's a quote from an e-mail from Myrna Larson that adds more to the gory details of this function: But I have an even better (i.e. worse) one: with dates Jan 1, 1960, Jan 1, 2003, and Basis = 2; result should be 43, but YEARFRAC gets 43.62778 !!! With the first date 1/1/1936, the error is 0.97778! Assuming that (perhaps) YEARFRAC is correct for periods up to 1 year, in order to take advantage of it's Day Count Basis argument, I wrote a new function that calculated the number of whole years from start date to last anniversary date, used YEARFRAC to calculate the fractional year from the anniversary to the final date, and added the 2 together. With a starting date in the year 1936 and testing against all dates in the year 2003, I get the same result for day count basis of 0 or 4, but differences for 1, 2, or 3. The typical difference equated to ~0.25 days for option 1, 357 days for option 2, and 17 days for option 3 (I got that by subtracting my result from YEARFRAC's and multiplying by 365.25). They have sure fouled something up with options 2 and 3! -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Mark Graesser" wrote in message ... Hi Norm, YEARFRAC works fine in Excel 97. If the dates are more then a year apart it produces a whole number for the full years with a decimal portion for the partial year. However, DATEDIF is definetly the better bet. YEARFRAC doesn't really handle leap years very well, and it requires loading the Analysis Toolpack. Regards, Mark Graesser ----- Norman Harker wrote: ----- Hi Lynn! If you use years and fractions of a year then you can use: =DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY (A1)))/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY( A1))-DATE(YEA R(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))) (Note: YEARFRAC produces errors where dates are 1 or more years apart). DATEDIF is undocumented except in Excel 2000 and is a built in function. See: Chip Pearson. http://www.cpearson.com/excel/datedif.htm -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Lynn" wrote in message ... I need to calculate benefit and severance end dates from a given employment termination date and do not know how to do it. For example: Termination Date: February 13. Employees receive 2 weeks severance/benefits per year of service, the weeks range anywhere from 2 to 50. I do not know how to enter a formula to calculate the severance/benefit end date. Thanks for any assistance you can provide. |
#5
|
|||
|
|||
CALCULATE FUTURE DATES
Hi Norman,
Thanks for the detail on this issue. When you said YEARFRAC produces erros I misinterpreted that to mean it returned an error message, not incorrect results. After reading your first post I played around with it a little and found that a 10 day span had a different year fraction if it crossed over feb 29, even though both ends were still in the same year. Now I see that YEARFRAC uses your logic in that it considers the year to be from the start date to the same date in the next year. I also tried a 14 year period starting 1/1/1990 and found that I had to be exactly 3 days short to get a YEARFRAC of exactly 14. It seemed to be using a 365 day year for all years even though I used a basis of 1. I filed the formula and macro from you and Myrna for future use. Thanks again, Mark Graesser |
#6
|
|||
|
|||
CALCULATE FUTURE DATES
Hi Mark!
"Think I'll avoid using this function." Agreed! Apart from periods of over a year, it won't produce what most people would regard as the correct fraction in many Leap Year calculations. Subject to the wording of any agreement, I think that Myrna and my solutions are safest for apportionments where fractions of a year are involved. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. |
Thread Tools | |
Display Modes | |
|
|