Error calculating date

A

Attie

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),MONTH($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...
 
R

Roger Govier

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top