Maybe

Dim sFormula As String

sFormula =
"=IF(C2="""",""1"",IF(AND(A2=""outdate"",E2<" "01/01/01"",E2<NOW()+14)," & _

"""2"",IF(AND(A2=""Birthday"",MONTH(E2)=MONTH(TODA Y())+(DAY(E2)-DAY(TODAY())
<0),DAY(e2)-DAY(TODAY())<=14)," & _
"""YES"",""NO"")))"
ActiveCell.Formula = sFormula


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jocker" wrote in message
. nl...
Many thanks, Roger, it works a treat.
I'm new to VBA so thought I would try it.
The code below however fails, can u tell me why ?

ActiveCell.Formula =

"=IF((C2)="""",""1"",IF(AND(A2)=""outdate"",(E2)< ""01/01/01"",(E2)<NOW()+14
),""2"",IF(AND((a2)=""Birthday"",MONTH(e2)=MONTH(T ODAY())+(DAY(e2)-DAY(TODAY
())<0),DAY(e2)-DAY(TODAY())<=14),""YES"",""NO""))"


Jeff
+++++++++++++++++++

"Roger Govier" wrote in message
...
Hi Arvi

Most unlike you.
One or two typo's (must be the remainder of the Christmas "spirits"
still
in the system<vbg)

Missing brackets after the Year(Today()) function

=AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MON
TH(A2),DAY(A2))=TODAY())

Also, Datedif error, should be
=AND(DATEDIF(A2,TODAY(),"MD")<15,DATEDIF(A2,TODAY( ),"MD")=0)
Datedif won't work in this scenario however, as the days part is giving
the number of days over and above elapsed months for the period, not the
days remaining.

Best wishes for the New Year

--
Regards

Roger Govier



Arvi Laanemets wrote:
Hi

With birth date in cell A2
=AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()
,MONTH(A2),DAY(A2))=TODAY())

You also can try
=AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD")


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )