# Conditional Format "birthday" for current week

1. ## Conditional Format "birthday" for current week

Hi all,

I currently use the following formula in Conditional Formatting to highlight a persons birthday:

=\$R1=DATE(YEAR(\$R1),MONTH(TODAY()),DAY(TODAY()))

which changes the colour of the cell on their birthday.

However, I need to amend this formula so that the highlighting applies to the current week, as in Sunday thru to Saturday.

My amendments have not been successful and you cannot use the WEEKNUM function in conditional formatting for some reason!

Any suggestions ...spellbound

2. ## Re: Conditional Format "birthday" for current week

Try

=AND(\$R1-(WEEKDAY(\$R1)-1)<=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),\$R1-WEEKDAY(\$R1)+6>=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

3. ## Re: Conditional Format "birthday" for current week

Hi Bob

Thanks for quick reply but could not get your solution to work at all.

Incidentally, R1 is the persons DOB formatted as ??-??-??.

Just to clarify the situation, I am trying to highlight the persons birthday by changing the colour of the cell, where their birthday falls within the current week and I need the conditional formatting to make this change of colour from the Sunday to the Saturday within the week that the birthday falls.

Hope this helps ...spellbound

4. ## Re: Conditional Format "birthday" for current week

You can't use Analysis ToolPak functions in conditional formatting, WEEKNUM is one of those.

Try this formula in conditional formatting

=DATE(YEAR(R1)+DATEDIF(R1,TODAY()-WEEKDAY(TODAY()),"Y")+1,MONTH(R1),DAY(R1))-TODAY()+WEEKDAY(TODAY())<=7

5. ## Re: Conditional Format "birthday" for current week

Thanks for that formula, it appears to be working fine.

I have carried out a test by adjusting the DOB for each day of the current week and it changes in the way that I want.

So, will mark this as solved ...spellbound

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1