Conditional Formatting Question Based on Birthday

[texaschai]

I'm sorry I can't find an answer to this, but I have a spreadsheet that I work off of for each pay period at my company, and in it there is a column for the employee dates of birth and a column for age. There are also two cells at the bottom of the worksheet that are pay period start date and end date. So, for example, the pay period start date is 1/1/14 and the end date is 1/15/14. When a person's age changes (they have a birthday within the pay period start and end dates), I'd like the column that shows their age to have conditional formatting to show that they had a birthday in that pay period.
What formula would I write in the conditional formatting formula box to show this?
Details:
Column F contains Date of Birth
Column G contains Age
Cell C47 has pay period start date
Cell C48 has pay period end date

I need this, by the way, to ensure that if they hit a certain age bracket, we change their Group Term Life deduction, so if there is an easier way to do what I need to do in order to get a similar result, please let me know. The age brackets are <25, 25-29, 30-34 and so on up to age 70. Can I conditionally format a cell when it moves from one age bracket to the next instead?

Thank you!
Charlotte

 

[liveinhope]

I'm sorry I can't find an answer to this, but I have a spreadsheet that I work off of for each pay period at my company, and in it there is a column for the employee dates of birth and a column for age. There are also two cells at the bottom of the worksheet that are pay period start date and end date. So, for example, the pay period start date is 1/1/14 and the end date is 1/15/14. When a person's age changes (they have a birthday within the pay period start and end dates), I'd like the column that shows their age to have conditional formatting to show that they had a birthday in that pay period.
What formula would I write in the conditional formatting formula box to show this?
Details:
Column F contains Date of Birth
Column G contains Age
Cell C47 has pay period start date
Cell C48 has pay period end date

I need this, by the way, to ensure that if they hit a certain age bracket, we change their Group Term Life deduction, so if there is an easier way to do what I need to do in order to get a similar result, please let me know. The age brackets are <25, 25-29, 30-34 and so on up to age 70. Can I conditionally format a cell when it moves from one age bracket to the next instead?

Thank you!
Charlotte

To hilight emps with birthday in pay period AND birthday takes them into next agegroup =AND(DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y"),MOD(DATEDIF($F2,$C$48,"Y"),5)=0)

to to highlight employees who have a "normal" birthday =DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y")

If you would like to do both enter both rules , making sure that the rules are in the correct order and select a different format (I like colours) for each rule

 

[texaschai]

To hilight emps with birthday in pay period AND birthday takes them into next agegroup =AND(DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y"),MOD(DATEDIF($F2,$C$48,"Y"),5)=0)

to to highlight employees who have a "normal" birthday =DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y")

If you would like to do both enter both rules , making sure that the rules are in the correct order and select a different format (I like colours) for each rule

Hi there, and thank you so much for your reply, but the conditional formatting formula typed as you show it above doesn't work. Am I supposed to substitute something for the "Y"?

 

[liveinhope]

It should work .

check the 2nd simpler rule first =DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y"). this compares results of 2 uses DATEDIFS

1st DATEDIF($F2,$C$47,"Y") is DOB (ie $f2) to start of pay period ($c$47) in years only "Y" ("Y" means Years )
2nd DATEDIF DATEDIF($F2,$C$48,"Y") IS dob to end of pay period $c$48

=DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y") just checks to see if the first DATEDIF returns a lower result than the 2nd DATEDIF. If this is true then the person has ha a birthday

Check for

- Did you apply the conditional formatting to the right cells ? My formula works if applied from row 2 to (say) row 10 . If your data starts on (say) row 10 you will need to adjust my formula

- Did you select a format after entering the formula (I've missed that step myself a few times)

</SPAN>	</SPAN>	</SPAN>
</SPAN>	</SPAN>	</SPAN>
</SPAN>	</SPAN>	</SPAN>
</SPAN>	</SPAN>	</SPAN>

<TBODY>
</TBODY>

 

[texaschai]

It should work .

check the 2nd simpler rule first =DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y"). this compares results of 2 uses DATEDIFS

1st DATEDIF($F2,$C$47,"Y") is DOB (ie $f2) to start of pay period ($c$47) in years only "Y" ("Y" means Years )
2nd DATEDIF DATEDIF($F2,$C$48,"Y") IS dob to end of pay period $c$48

=DATEDIF($F2,$C$47,"Y") < DATEDIF($F2,$C$48,"Y") just checks to see if the first DATEDIF returns a lower result than the 2nd DATEDIF. If this is true then the person has ha a birthday

Check for

- Did you apply the conditional formatting to the right cells ? My formula works if applied from row 2 to (say) row 10 . If your data starts on (say) row 10 you will need to adjust my formula

- Did you select a format after entering the formula (I've missed that step myself a few times)

<tbody>
</tbody>

OK, at first the formula wasn't even being accepted because I forgot the spaces around the "<". (Sorry!) But I have now applied it to one particular age column cell (with color) for a person who has a DOB of 1/18/84 (young whippersnapper) and the pay period start date is 1/16/14 and end date is 1/31/14, so he had a birthday in that pay period, but his cell is not changing to red as I would expect.

Also, how do I copy this same conditional formatting all the way down the column? I used to recall you could paste special/values for just conditional formatting, but now it says "all merging conditional formats". What the hell does that mean? It didn't work, whatever it is.

Thank you for helping me troubleshoot!

 

[liveinhope]

q1 -what version of excel are you using ?
q2 - double chdeck that c47 = period start and c48 = period end

 

[texaschai]

q1 -what version of excel are you using ?
q2 - double chdeck that c47 = period start and c48 = period end

OMG. I'm an idiot. I added a person (row) in my sheet since I first sent this query and I totally spaced on him. (Poor fella).
You're amazing! Thank you!!

 

[liveinhope]

good to hear that its working now