Highlight birthdays

  • Thread starter Thread starter jocker
  • Start date Start date
J

jocker

Using conditional formatting how would I highlight birthdays coming up
within the next 14 days.
I can't use < today()+14 since birth year is in the past. I don't mind
using VBA if this helps
 
Hi

There may be a simpler formula, but the following seems to work
=AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TODAY())<=14)
 
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(),A2)>=0,"MD")
 
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()),MONTH(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
 
Another example

=AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>=TODAY(),DATE(YEAR(TODAY()),MONTH
(A1),DAY(A1))-TODAY()<=14)

Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at why.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
0),DAY(A1)-DAY(TODAY())<
<0),DAY(A1)-DAY(TODAY())<
wrote
=AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TOD
AY())< =14)
Click to expand...

Hi Bob

You're quite right.
My formula is absolute rubbish, as it would also give incorrect results
to any other days in the next month which are less than
Today's DAY().

Yours is the far nicer (and correct) solution.
Happy New Year to you.
 
Hi Bob

Apologies for that last response, I have made some changes to OE6 and it
is putting some of my responses in weird positions relative to the
original post

It should have appeareed as

You're quite right.
My formula is absolute rubbish, as it would also give incorrect
results to any other days in the next month which are less than
Today's DAY().

Yours is the far nicer (and correct) solution.
Happy New Year to you.
 
:-))

Thanks for correction! (I simply didn't have enough time to read what i did
write - too much work)

The formula with DATEDIF must be
=IF(ISERROR(DATEDIF(TODAY(),A2,"MD")),FALSE,(DATEDIF(TODAY(),A2,"MD")<15))
(the old one checked for past birthdays, not for coming ones, and missed
error checking)



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )




Roger Govier said:
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()),MONTH(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
Click to expand...
 
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(TODAY())+(DAY(e2)-DAY(TODAY())<0),DAY(e2)-DAY(TODAY())<=14),""YES"",""NO""))"


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

Roger Govier said:
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()),MONTH(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
Click to expand...
 
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(TODAY())+(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 said:
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(TODAY())+(DAY(e2)-DAY(TODAY
())<0),DAY(e2)-DAY(TODAY())<=14),""YES"",""NO""))"


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

Roger Govier said:
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
Click to expand...
=AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MON
TH(A2),DAY(A2))>=TODAY())
Click to expand...
 

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

Similar Threads

Formula to indicate upcoming birthdays 5
Epic Games freebies 2
conditional formatting 1
Excel formula 2
create calculated field for age in pivot tables 4
Excel Send emails based on Dates in an EXCEL sheet. 0
Days until birthday 1
Countif if counted results are based on formula 6

Back
Top