A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

CALCULATE FUTURE DATES



 
 
Thread Tools Display Modes
  #1  
Old February 4th, 2004, 07:05 PM
Michael J. Malinsky
external usenet poster
 
Posts: n/a
Default 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  
Old February 4th, 2004, 09:41 PM
Mark Graesser
external usenet poster
 
Posts: n/a
Default 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.
  #3  
Old February 4th, 2004, 10:13 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default CALCULATE FUTURE DATES

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.



  #4  
Old February 5th, 2004, 03:54 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 05:01 PM
Mark Graesser
external usenet poster
 
Posts: n/a
Default 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  
Old February 5th, 2004, 05:42 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.