software

Using conditional formatting how would I highlight birthdays coming up
within the next 14 days.
I can't use lt; 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())lt;0),DAY(A1)-DAY(TODAY())lt;=14)--
Regards

Roger Govier
jocker gt; wrote:
gt; Using conditional formatting how would I highlight birthdays coming up
gt; within the next 14 days.
gt; I can't use lt; today() 14 since birth year is in the past. I don't
gt; mind using VBA if this helps
Hi

With birth date in cell A2
=AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))gt;=T ODAY())

You also can try
=AND(DATEDIF(TODAY(),A2,quot;MDquot;)lt;15,DATEDIF(TODAY(),A 2)gt;=0,quot;MDquot;)--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )quot;jockerquot; gt; wrote in message
. nl...
gt; Using conditional formatting how would I highlight birthdays coming up
gt; within the next 14 days.
gt; I can't use lt; today() 14 since birth year is in the past. I don't mind
gt; using VBA if this helps
gt;
Hi Arvi

Most unlike you.
One or two typo's (must be the remainder of the Christmas quot;spiritsquot;
still in the systemlt;vbggt;)

Missing brackets after the Year(Today()) function
=AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))gt;= TODAY())

Also, Datedif error, should be
=AND(DATEDIF(A2,TODAY(),quot;MDquot;)lt;15,DATEDIF(A2,TODAY( ),quot;MDquot;)gt;=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 gt; wrote:
gt; Hi
gt;
gt; With birth date in cell A2
gt; =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()
gt; ,MONTH(A2),DAY(A2))gt;=TODAY())
gt;
gt; You also can try
gt; =AND(DATEDIF(TODAY(),A2,quot;MDquot;)lt;15,DATEDIF(TODAY(),A 2)gt;=0,quot;MDquot;)
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
Another example

=AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))gt;=TODAY( ),DATE(YEAR(TODAY()),MONTH
(A1),DAY(A1))-TODAY()lt;=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)

quot;Roger Govierquot; gt; wrote in message
...
gt; Hi
gt;
gt; There may be a simpler formula, but the following seems to work
gt;
=AND(MONTH(A1)=MONTH(TODAY()) (DAY(A1)-DAY(TODAY())lt;0),DAY(A1)-DAY(TODAY())lt;
=14)
gt;
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt; jocker gt; wrote:
gt; gt; Using conditional formatting how would I highlight birthdays coming up
gt; gt; within the next 14 days.
gt; gt; I can't use lt; today() 14 since birth year is in the past. I don't
gt; gt; mind using VBA if this helps
gt;
gt;
0),DAY(A1)-DAY(TODAY())lt;
lt;0),DAY(A1)-DAY(TODAY())lt;
gt; wrote
gt;gt; Hi
gt;gt; There may be a simpler formula, but the following seems to work
gt; =AND(MONTH(A1)=MONTH(TODAY()) (DAY(A1)-DAY(TODAY())lt;0),DAY(A1)-DAY(TOD
gt; AY())lt; =14)
gt;gt; --
gt;gt; Regards
gt;gt; Roger Govier
gt;gt; jocker gt; wrote:
gt;gt;gt; Using conditional formatting how would I highlight birthdays coming
gt;gt;gt; up within the next 14 days.
gt;gt;gt; I can't use lt; today() 14 since birth year is in the past. I don't
gt;gt;gt; mind using VBA if this helps

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.--
Regards

Roger Govier
Bob Phillips gt; wrote:
gt; Another example
gt;
gt; =AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))gt;=TODAY( ),DATE(YEAR(TODAY())
gt; ,MONTH (A1),DAY(A1))-TODAY()lt;=14)
gt;
gt; Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at
gt; why.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Roger Govierquot; gt; wrote in message
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.
--
Regards

Roger Govier
Roger Govier gt; wrote:
gt; 0),DAY(A1)-DAY(TODAY())lt;
gt; lt;0),DAY(A1)-DAY(TODAY())lt;
gt;gt; wrote
gt;gt;gt; Hi
gt;gt;gt; There may be a simpler formula, but the following seems to work
gt;gt; =AND(MONTH(A1)=MONTH(TODAY()) (DAY(A1)-DAY(TODAY())lt;0),DAY(A1)-DAY(TOD
gt;gt; AY())lt; =14)
gt;gt;gt; --
gt;gt;gt; Regards
gt;gt;gt; Roger Govier
gt;gt;gt; jocker gt; wrote:
gt;gt;gt;gt; Using conditional formatting how would I highlight birthdays coming
gt;gt;gt;gt; up within the next 14 days.
gt;gt;gt;gt; I can't use lt; today() 14 since birth year is in the past. I don't
gt;gt;gt;gt; mind using VBA if this helps
gt;
gt; Hi Bob
gt;
gt; You're quite right.
gt; My formula is absolute rubbish, as it would also give incorrect
gt; results to any other days in the next month which are less than
gt; Today's DAY().
gt;
gt; Yours is the far nicer (and correct) solution.
gt; Happy New Year to you.
gt;
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt; Bob Phillips gt; wrote:
gt;gt; Another example
gt;gt;
gt;gt; =AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))gt;=TODAY( ),DATE(YEAR(TODAY())
gt;gt; ,MONTH (A1),DAY(A1))-TODAY()lt;=14)
gt;gt;
gt;gt; Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at
gt;gt; why.
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Bob Phillips
gt;gt;
gt;gt; (remove nothere from email address if mailing direct)
gt;gt;
gt;gt; quot;Roger Govierquot; gt; wrote in message
:-))

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,quot;MDquot;)),FALSE,(DATED IF(TODAY(),A2,quot;MDquot;)lt;15))
(the old one checked for past birthdays, not for coming ones, and missed
error checking)
--
Arvi Laanemets
( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )

quot;Roger Govierquot; gt; wrote in message
...
gt; Hi Arvi
gt;
gt; Most unlike you.
gt; One or two typo's (must be the remainder of the Christmas quot;spiritsquot; still
gt; in the systemlt;vbggt;)
gt;
gt; Missing brackets after the Year(Today()) function
gt; =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))gt;= TODAY())
gt;
gt; Also, Datedif error, should be
gt; =AND(DATEDIF(A2,TODAY(),quot;MDquot;)lt;15,DATEDIF(A2,TODAY( ),quot;MDquot;)gt;=0)
gt; Datedif won't work in this scenario however, as the days part is giving
gt; the number of days over and above elapsed months for the period, not the
gt; days remaining.
gt;
gt; Best wishes for the New Year
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt; Arvi Laanemets gt; wrote:
gt;gt; Hi
gt;gt;
gt;gt; With birth date in cell A2
gt;gt; =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()
gt;gt; ,MONTH(A2),DAY(A2))gt;=TODAY())
gt;gt;
gt;gt; You also can try
gt;gt; =AND(DATEDIF(TODAY(),A2,quot;MDquot;)lt;15,DATEDIF(TODAY(),A 2)gt;=0,quot;MDquot;)
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Arvi Laanemets
gt;gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
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 =
quot;=IF((C2)=quot;quot;quot;quot;,quot;quot;1quot;quot;,IF(AND(A2)=quot;quot;outdatequot;quot;,(E2)lt;gt; quot;quot;01/01/01quot;quot;,(E2)lt;NOW() 14),quot;quot;2quot;quot;,IF(AND((a2)=quot;quot;Birthdayquot;quot; ,MONTH(e2)=MONTH(TODAY()) (DAY(e2)-DAY(TODAY())lt;0),DAY(e2)-DAY(TODAY())lt;=14),quot;quot;YESquot;quot;,quot;quot;NOquot;quot;))quot;Jeff


quot;Roger Govierquot; gt; wrote in message
...
gt; Hi Arvi
gt;
gt; Most unlike you.
gt; One or two typo's (must be the remainder of the Christmas quot;spiritsquot; still
gt; in the systemlt;vbggt;)
gt;
gt; Missing brackets after the Year(Today()) function
gt; =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))gt;= TODAY())
gt;
gt; Also, Datedif error, should be
gt; =AND(DATEDIF(A2,TODAY(),quot;MDquot;)lt;15,DATEDIF(A2,TODAY( ),quot;MDquot;)gt;=0)
gt; Datedif won't work in this scenario however, as the days part is giving
gt; the number of days over and above elapsed months for the period, not the
gt; days remaining.
gt;
gt; Best wishes for the New Year
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt; Arvi Laanemets gt; wrote:
gt;gt; Hi
gt;gt;
gt;gt; With birth date in cell A2
gt;gt; =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()
gt;gt; ,MONTH(A2),DAY(A2))gt;=TODAY())
gt;gt;
gt;gt; You also can try
gt;gt; =AND(DATEDIF(TODAY(),A2,quot;MDquot;)lt;15,DATEDIF(TODAY(),A 2)gt;=0,quot;MDquot;)
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Arvi Laanemets
gt;gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
Maybe

Dim sFormula As String

sFormula =
quot;=IF(C2=quot;quot;quot;quot;,quot;quot;1quot;quot;,IF(AND(A2=quot;quot;outdatequot;quot;,E2lt;gt;quot; quot;01/01/01quot;quot;,E2lt;NOW() 14),quot; amp; _

quot;quot;quot;2quot;quot;,IF(AND(A2=quot;quot;Birthdayquot;quot;,MONTH(E2)=MONTH(TODA Y()) (DAY(E2)-DAY(TODAY())
lt;0),DAY(e2)-DAY(TODAY())lt;=14),quot; amp; _
quot;quot;quot;YESquot;quot;,quot;quot;NOquot;quot;)))quot;
ActiveCell.Formula = sFormula--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;jockerquot; gt; wrote in message
. nl...
gt; Many thanks, Roger, it works a treat.
gt; I'm new to VBA so thought I would try it.
gt; The code below however fails, can u tell me why ?
gt;
gt; ActiveCell.Formula =
gt;
quot;=IF((C2)=quot;quot;quot;quot;,quot;quot;1quot;quot;,IF(AND(A2)=quot;quot;outdatequot;quot;,(E2)lt;gt; quot;quot;01/01/01quot;quot;,(E2)lt;NOW() 14
),quot;quot;2quot;quot;,IF(AND((a2)=quot;quot;Birthdayquot;quot;,MONTH(e2)=MONTH(T ODAY()) (DAY(e2)-DAY(TODAY
())lt;0),DAY(e2)-DAY(TODAY())lt;=14),quot;quot;YESquot;quot;,quot;quot;NOquot;quot;))quot;
gt;
gt;
gt; Jeff
gt;
gt;
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt; gt; Hi Arvi
gt; gt;
gt; gt; Most unlike you.
gt; gt; One or two typo's (must be the remainder of the Christmas quot;spiritsquot;
still
gt; gt; in the systemlt;vbggt;)
gt; gt;
gt; gt; Missing brackets after the Year(Today()) function
gt; gt;
=AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()),MON
TH(A2),DAY(A2))gt;=TODAY())
gt; gt;
gt; gt; Also, Datedif error, should be
gt; gt; =AND(DATEDIF(A2,TODAY(),quot;MDquot;)lt;15,DATEDIF(A2,TODAY( ),quot;MDquot;)gt;=0)
gt; gt; Datedif won't work in this scenario however, as the days part is giving
gt; gt; the number of days over and above elapsed months for the period, not the
gt; gt; days remaining.
gt; gt;
gt; gt; Best wishes for the New Year
gt; gt;
gt; gt; --
gt; gt; Regards
gt; gt;
gt; gt; Roger Govier
gt; gt;
gt; gt;
gt; gt;
gt; gt; Arvi Laanemets gt; wrote:
gt; gt;gt; Hi
gt; gt;gt;
gt; gt;gt; With birth date in cell A2
gt; gt;gt; =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()lt;15,DATE(YEAR(TODAY()
gt; gt;gt; ,MONTH(A2),DAY(A2))gt;=TODAY())
gt; gt;gt;
gt; gt;gt; You also can try
gt; gt;gt; =AND(DATEDIF(TODAY(),A2,quot;MDquot;)lt;15,DATEDIF(TODAY(),A 2)gt;=0,quot;MDquot;)
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Arvi Laanemets
gt; gt;gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt; gt;
gt; gt;
gt;
gt;