Conditional Formatting Question Based on Birthday

T

texaschai

New Member
Joined
Feb 4, 2014
Messages
34
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
texaschai said:
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
Click to expand...

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
 
Upvote 0
liveinhope said:
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
Click to expand...

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"?
 
Upvote 0
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)

[TABLE="width: 223"]
<TBODY>[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
liveinhope said:
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)

[TABLE="width: 223"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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!
 
Upvote 0
q1 -what version of excel are you using ?
q2 - double chdeck that c47 = period start and c48 = period end
 
Upvote 0
liveinhope said:
q1 -what version of excel are you using ?
q2 - double chdeck that c47 = period start and c48 = period end
Click to expand...

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!!
:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Display age range and age based on date entered and actual calendar date.
Replies
6
Views
322
dreid1011
dreid1011
C
  • Question Question
Conditional Formatting based on age range
2
Replies
17
Views
2K
CMW
C
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
290
chris_bosten
C
W
  • Solved
Copying Conditional Formatting with relative values
Replies
9
Views
1K
weeksy
W
I
  • Question Question
Unable to change date format
Replies
2
Views
299
Imajica
I

Forum statistics

Threads
1,223,164
Messages
6,170,435
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top