Date Difference expressed in Completed Yrs, Mths & Days
Posted by Peter Forrester on November 13, 2001 8:08 PM
Apologies in advance for the lengthy explanation ... but this is proving to be a bit of a challenge.
Excel Version 97 SR2
I am working on a spreadsheet which calculates a variety of Employee Benefits, many of which depend on the period of employment expressed in terms of completed Years, Months and Days. Although this appears to be simple, it is complicated by what I will call "month-end" issues.
The following formulae work in >>mostYears =DATEDIF(C3,D3+1,"y")
Months =DATEDIF(C3,D3+1,"ym")
Days =DATEDIF(C3,D3+1,"md")
For example [NOTE: dates are in dd/mm/yyyy format]
DateFrom DateTo Yrs Mths Days Correct?
17/08/1998 25/10/2002 4 2 9 Y
25/10/1998 17/08/2002 3 9 24 Y
23/09/1958 14/11/2001 43 1 23 Y
Where I run into trouble is illustrated in the examples below:
DateFrom DateTo Yrs Mths Days Correct?
01/11/1999 27/02/2000 0 4 27 Y
01/11/1999 28/02/2000 0 4 28 Y
01/11/1999 29/02/2000 0 5 0 Y
01/11/1999 01/03/2000 0 5 1 Y
01/11/1999 02/03/2000 0 5 2 Y
01/11/1999 03/03/2000 0 5 3 Y
01/11/1999 04/03/2000 0 5 4 Y
Now change the DateFrom to 31/10/1999
31/10/2001 27/02/2002 0 3 28 Y
31/10/2001 28/02/2002 0 4 -2 N
31/10/2001 01/03/2002 0 4 -1 N
31/10/2001 02/03/2002 0 4 0 N
31/10/2001 03/03/2002 0 4 1 N
31/10/2001 04/03/2002 0 4 2 N
I suppose I could write a VB routine to deal with cases like this, but since I know very little about VB I was hoping that there might be a simpler, more elegant solution.
Any help would be most appreciated.