Urgent..Need Help for date calculation

[ashish navale]

Hi,

I have a report in which there are no. of dates given. In that report I have to find out any date which is greater than one month in past....and any date which is greater than six months in future..month should be given more importance rather than days. Because for e.g if today is 07/30/2011 then date 05/31/2011 should be captured and no date of June month should be captured. Also if any dates are of previous years that should be captured. I had a formula which worked for above criteria.

=IF(TEXT(TODAY(),"YYYY")TEXT(A6,"YYYY"),"YES",IF(TEXT(TODAY(),"YYYY")>TEXT(A6,"YYYY"),"YES",IF(TEXT(TODAY(),"MM")-TEXT(A6,"MM")>1,"YES","NO")))

The above formula works only for dates greater than one month in past. But if any given date is of greater than six months in future it is unable to catch. I only need is that extend the above formula so that it will catch both criteria..i.e, If any date is greater than one month in past or any date greater than six months in future it shud show as "YES" else "NO"


I request you to plz help me on above.

 

[fred]

Are you missing something, like a sign + - * / within your formula?


hint: IF(TEXT(TODAY(),"YYYY")TEXT(A6,"YYYY") where there is no sign after "YYYY") and TEXT(A6,"YYYY")

 

[Hui]

Ashish


There also seems to be a few <, > or = missing?


I'd go for something like: =IF(AND(TODAY()-A6<30,TODAY()-A6>-180),"Yes","No")