Error calculating date
 

hi,

please help me to resolve this issue, the following formula works fine till
31 Dec 2005, when year changes to 2006 it just stop caculating the date,
formula I'm using to caculate date & year is:
=IF(YEAR($E$2)-YEAR(E9)=2,IF(12-MONTH(E9)0,12-MONTH(E9)+IF(DAY($E$2)=DAY(E9),MONTH($E$2),0),MON TH($E$2)),0)



Date 25/01/2007 (<--- This is the current date)

D 28 December 2005 P5 1
D 01 December 2005 P3 1
D 15 December 2005 P4 1
D 13 January 2006 P4 0 (<----This Value always came wrong)
D 25 October 2005 P2 3
D 01 December 2005 P5 1
D 29 October 2005 P5 2
D 22 December 2005 P1 1
D 31 December 2005 P3 1


Please help me out...

Error calculating date
 

Hi

It gives 0 because it fails on the first test
Year($E$2)-Year(E9) =1

Maybe you could use
=MIN(12,MONTH(E2)-MONTH(E9)+12)

--
Regards

Roger Govier


"Attie" wrote in message
...
hi,

please help me to resolve this issue, the following formula works fine
till
31 Dec 2005, when year changes to 2006 it just stop caculating the
date,
formula I'm using to caculate date & year is:
=IF(YEAR($E$2)-YEAR(E9)=2,IF(12-MONTH(E9)0,12-MONTH(E9)+IF(DAY($E$2)=DAY(E9),MONTH($E$2),0),MON TH($E$2)),0)



Date 25/01/2007 (<--- This is the current date)

D 28 December 2005 P5 1
D 01 December 2005 P3 1
D 15 December 2005 P4 1
D 13 January 2006 P4 0 (<----This Value always came
wrong)
D 25 October 2005 P2 3
D 01 December 2005 P5 1
D 29 October 2005 P5 2
D 22 December 2005 P1 1
D 31 December 2005 P3 1


Please help me out...