+ Reply to Thread
Results 1 to 5 of 5

Conditional Format "birthday" for current week

  1. #1
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    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
    Last edited by Spellbound; 07-13-2009 at 06:07 PM.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,283

    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. #3
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    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. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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. #5
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    Re: Conditional Format "birthday" for current week

    Hi daddylonglegs

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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