Conditional Formatting - Birthdays

  • Thread starter Thread starter Steevo
  • Start date Start date
S

Steevo

I am currently creating a list of dates of birth of everyone I know in Excel
to ensure I cannot forget their birthdays. Is there a formula I can use with
conditional formatting to work out the number of days in until their next
birthday and then distinguish between dates that are within 7 days of the
current date by highlighting them red?


Many Thanks
 
How about:
=(if(date(year(today()),month(a1),day(a1))<today(),date(year(today())+1,mont
h(a1),day(a1)),date(year(today()),month(a1),day(a1)))-today())<=7
 
With DOB in A1, =TODAY() in B1, try something like this for your CF
formula -

=366-DATEDIF(A1,B1,"YD")<8

HTH,
Andy
 
Stephen Bye said:
How about:
=(if(date(year(today()),month(a1),day(a1))<today(),date(year(today())+1,mont
h(a1),day(a1)),date(year(today()),month(a1),day(a1)))-today())<=7
Click to expand...

Thanks for your help stephen, unfortunatly I can't seem to get that formula
to work because by inputting the year as "today()" all birthdays are thought
of as being this current year. This means that any birthdays in the first
7days of January will not be alerted until the first day of the new year
i.e. 1st Jan and therefore it cannot warn you in advance of birthdays on the
1st Jan.

Many thanks again
 
Andy Brown said:
With DOB in A1, =TODAY() in B1, try something like this for your CF
formula -

=366-DATEDIF(A1,B1,"YD")<8

HTH,
Andy
Click to expand...

Thanks also for your help, unfortunatly I cannot get your formula to work
either because in assuming there are 366 (as opposed to 365.25) days in the
year over thirty years or so the 0.75 error is multiplied. Have tried to use
365.25 but that will not work either because it adds on a day or so here or
there which i think is also due to error.


Thanks again!
 
Here's another try:

With date of birth in A1 and TODAY() in B2:

=(IF(DATE(0,MONTH(B1),DAY(B1))>DATE(0,MONTH(A1),DAY(A1)),DATE(1,MONTH(DATE(0,MON
TH(A1),DAY(A1))),DAY(DATE(0,MONTH(A1),DAY(A1))))-DATE(0,MONTH(B1),DAY(B1)),DATE(
0,MONTH(A1),DAY(A1))-DATE(0,MONTH(B1),DAY(B1)))<=7)

What a monster!

To make a formula like this just a little bit understandable, I would split the
formula into a couple of (hidden) helper columns.

Regards
Anders Silvén
 
Anders S said:
Here's another try:

With date of birth in A1 and TODAY() in B2:

=(IF(DATE(0,MONTH(B1),DAY(B1))>DATE(0,MONTH(A1),DAY(A1)),DATE(1,MONTH(DATE(0
,MON
TH(A1),DAY(A1))),DAY(DATE(0,MONTH(A1),DAY(A1))))-DATE(0,MONTH(B1),DAY(B1)),D
ATE(
0,MONTH(A1),DAY(A1))-DATE(0,MONTH(B1),DAY(B1)))<=7)

What a monster!

To make a formula like this just a little bit understandable, I would split the
formula into a couple of (hidden) helper columns.

Regards
Anders Silvén
Click to expand...

Is that one designed for use with the conditional formatting? If so where
(and how?) do i put it in? I'm not sure what should come after "Cell value
is" box.

Many Thanks
 
Hi Steevo,

Define a name (Ctrl-F3) Delta reference to : ={0;1}

In conditional formatting, pull down the first little menu to highlight
"Formula is" (instead of "Cell Value")
Assuming your date in A1, the following formula (it's fundamentaly an array
formula but conditional formatting doesn't require/accept
ctrl-shift-enter_ing , it's a detail but useful if you want to enter the
formula into a spreadsheet):

=MIN(MOD(DATE(YEAR(TODAY())+Delta,MONTH(A1),DAY(A1))-TODAY(),733))<=7

For today (Sept 6), This will highlight birthdays from Sept 6 thru 13.
Not sure if this is what you want.

Regards,

Daniel M.
 
Sorry, forgot that part.

In Conditional formatting for the DOB-cell (A1), choose Formula is (instead of
Cell value is) and paste the formula.

Regards
Anders
 
Perhaps I was wrong to try and compensate.

In B1:B40, I entered =TODAY() ; in A1:A40 30-Aug-03, 30-Aug-02 ...
30-Aug-64. The formula
=DATEDIF(A1,B1,"yd")
in C1:C40 returned 7 every time.

So I applied a CF formula of
=DATEDIF(A1,B1,"yd")<8
to B1:B40 and it worked (highlighted all red).

I changed A1:A40 to 29-Aug-03, 29-Aug-02 ... 29-Aug-64
and the highlighting was removed.

HTH,
Andy
 
If that is the case then you haven't entered it correctly; it works for me
for all dates, including the first week of January.
It basically says:
if current-year-birthday is before today
then number of days until birthday = next-year-birthday minus today
else number of days until birthday = current-year-birthday minus today
 
Andy Brown said:
Yeah, you'n'me both mate.

For red, =DATEDIF(L2,$M$1,"yd")<8

For yellow, =DATEDIF(L2,$M$1,"yd")<15

For green, =AND(DATEDIF(L2,$M$1,"yd")>14,DATEDIF(L2,$M$1,"yd")<29)

I put hard dates rather than =TODAY() in M1 for testing, but you could
substitute.
Click to expand...

Wahay!! thanks for that mate - brain can now have a rest for a while! I
struggled a bit to get it to work and in the end all I needed to change was
substituting "TODAY()" in for M1 and also I swapped it round so it read:

=DATEDIF(TODAY(),L2)<8

so that the result was FALSE.

Thanks again!!!
 
Back
Top