+ Reply to Thread
Results 1 to 3 of 3

My date highlights are also on the next month

Hybrid View

  1. #1
    Registered User
    Join Date
    11-18-2012
    Location
    Riverstone
    MS-Off Ver
    Excel 2007
    Posts
    2

    My date highlights are also on the next month

    Hi all

    I have a small problem, I have tried everything, I only have the last thing to do.
    as you can see from the image, my date highlights are also on the next month (Dec) I need them to only go to the current date that in the Starts & finish dates (for this Nov)
    my code:

    Activity Name (cell A) is pick from a list, works ok
    Duration (Days) (cell B) is work out by this =IF(C5=D5,1,DAYS360(C5,D5)+1) works OK, but I have to +1 to fix the days
    cells with no dates shows a 0 so I use this conditional formatting =ISBLANK(C5) & =ISBLANK(D5) this colors the 0 white
    I also have conditional formatting to make the cell B red if the dates are done wrong =C5>D5
    now the conditional formatting to make the blue color in the days cells =AND(E$4>=DAY($C5), E$4<=DAY($D5))
    this work great, but it also shows the blue in the other months, I only want it to show for the month shown in the cel C & D
    I tried this =IF( ABS(MONTH(C5) = MONTH(E2)),AND(E$4>=DAY($C5), E$4<=DAY($D5)),"") this works but I will have to add it to every cel needed, like 366 for a year, I really need a conditional formatting to do this, I did try this

    conditional formatting =AND(E$4>=DAY($C5), E$4<=DAY($D5))
    conditional formatting =ABS(MONTH(C5) = MONTH(E2)) (tick stop if true)

    this does not work, can anyone help please

    Capture.PNG

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,620

    Re: My date highlights are also on the next month

    Perhaps post a sample sheet. A picture is nice but useless

  3. #3
    Registered User
    Join Date
    11-18-2012
    Location
    Riverstone
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: My date highlights are also on the next month

    OK Pepe, here is the sheet
    Thank you
    Activity.xlsm

+ 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