Conditional Formatting

[Dave]

I have a date field (birthdates) in column B
In column A I do math on that date field to arrive at age
(Today()-C3)/365) - (Not very accurate but works for my needs)
I would like Column A to be conditionally formatted based on the Date (month
and day) in Column B.
I want the text to turn RED if we are approaching X number of days before
that date (say 45).

How can I do this?

Thanks

Dave

 

[Kidaeshus]

I have a date field (birthdates) in column B
In column A I do math on that date field to arrive at age
(Today()-C3)/365) - (Not very accurate but works for my needs)
I would like Column A to be conditionally formatted based on the Date (month
and day) in Column B.
I want the text to turn RED if we are approaching X number of days before
that date (say 45).

How can I do this?

It is a little confusing as you talk about columns A and B yet refer
to C3 in your formula, but I think this should satisfy the True or
False for conditional formatting. I'm using A and B as described, and
this compensates for birthdays gone past.

=IF(IF(DATE(YEAR(A1),MONTH(B1),DAY(B1))-
A1<0,DATE(YEAR(A1)+1,MONTH(B1),DAY(B1))-
A1,DATE(YEAR(A1),MONTH(B1),DAY(B1))-A1)<46,TRUE,FALSE)

If you want a second (actually first in a series of two conditions)
that highlights a different colour on the actual birthday, simplify
the aforementioned formula to read:

=IF(DATE(YEAR(A1),MONTH(B1),DAY(B1))-A1=0,TRUE,FALSE)


HTHs.

 

[Dave]

You are correct - my formula in the A column should be
(Today()-B3)/365)

Column A is formatted as a number
Column B is formatted as a Date

I could no get either of your formulas to work

 

[Kidaeshus]

You are correct - my formula in the A column should be
(Today()-B3)/365)

Column A is formatted as a number
Column B is formatted as a Date

I could no get either of your formulas to work

Sorry, my formula presupposed that A1 held =TODAY(). It constructs a
date from the day & month of the birthdate together with the year of
TODAY() and calculates the difference. For birthdays gone past (e.g. a
difference of less than zero), it adds a year to TODAY(). Substituting
TODAY() for all references of A1, this changes the formulas to:

=IF(IF(DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))-
TODAY()<0,DATE(YEAR(TODAY())+1,MONTH(B1),DAY(B1))-
TODAY(),DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))-TODAY())<46,TRUE,FALSE)

=IF(DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))-TODAY()=0,TRUE,FALSE)

I think this may resolve things.

 

[Dave]

still no love.

I maybe applying it wrong

I will keep looking at it

Thanks for the replies

Dave

 

[Kidaeshus]

still no love.

OK, I figured out what you were trying to do with the formula in A1.
This would be to represent the number of years old of the individual
with the birthdate in B1. Since anything over xx.5 will average up to
the next year, it should instead be

=INT((TODAY()-B1)/365)

with custom formatting as 0"yo" as in 35yo for someone with 19/03/1974
in B1.

With your condition of 45 days, we can calculate that 45 days is
0.123288 of a year, so 1-(0.123288) is 0.876712 (remember that
number!)

With the focus on A1, go to Conditional Formatting (in Excel 2007)
through Home->Styles->Conditional Formatting->New Rule->Use a formula
to determine which cells to format. Where is says "Format values where
this value is true" put in:

=IF(MOD((TODAY()-B1)/365,1)>0.876712,TRUE,FALSE)

....and apply text colour or highlighting as required. Press OK.

That should do it. You can propagate the entire column A with Fill
Down (Ctrl+D)

HTHs.

 

[Dave]

Seems I was testing this on one of those rare dates that are just not
working due to math errors in using 365 days.
My Birthday 2/18/1951

shows me as 59 - I will not be for 3 days.

Your conditional formatting was not working on this date (my test date) and
I did not try it on any other date.
When I did - it does now seem to work

Thanks for your help

Dave

 

[Kidaeshus]

shows me as 59 - I will not be for 3 days.

Try 365.25 to get a closer approximation ("Not very accurate but works
for my needs") or use the formula that constructs the subtrahend date
from the birthdate's day & month together with the current year for an
exact comparison.

YW.