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.

Posted by Richard S on November 13, 2001 9:19 PM

See saga at 5646.html (nt)

Posted by Barrie Davidson on November 13, 2001 9:25 PM

More specifically, 5646.html (nt)

Posted by Barrie Davidson on November 13, 2001 9:30 PM

saga, I like the sound of that! DATEDIF, The Saga..... :-)

Posted by Omar Sivori on November 13, 2001 11:44 PM

As suggested by Mark W ......


.... take a look at :-

http://support.microsoft.com/support/kb/articles/Q95/9/48.ASP?LN=EN-US&SD=gn&FR=0&qry=dates&rnk=23&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

However .... whatever formula you use, anomalies will occur !



Posted by Peter Forrester on November 14, 2001 11:45 PM

Thanks for the prompt response ... I'll need to look into this a little deeper