+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting with month and 'Dates Occurring'

  1. #1
    Registered User
    Join Date
    12-14-2008
    Location
    Minneapolis, MN
    Posts
    25

    Conditional Formatting with month and 'Dates Occurring'

    Hi Guys,

    I'm trying to keep track of training dates for employees.

    I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.

    I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.

    Is there a better way to do this?

    Thank you for your help,
    Ray
    Last edited by N164PJ; 12-22-2009 at 03:15 PM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Conditional Formatting with month and 'Dates Occurring'

    Hi,

    if you test for month only, then it probably checks if the next month is bigger than the current month, so for this month = 12, next month = 1, the condition will be false.

    post the formula you're using and we'll see how we can change it to include the year as well.

    cheers

  3. #3
    Registered User
    Join Date
    12-14-2008
    Location
    Minneapolis, MN
    Posts
    25

    Re: Conditional Formatting with month and 'Dates Occurring'

    Thank you teylyn,

    I was hoping to keep this spreadsheet simple, therefore I haven't resorted to a formula in the conditional statement-yet.

    My understanding is that by using "Dates Occurring" and "Next Month" in the conditional formula, the program will always be able to identify a cell containing that date (always relative to the current date and time). I'm surprised that it doesn't know that Jan 2010 is the month following Dec 2009. Am I going to have to use a formula to get around this?

    Thank you again,
    Ray

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Conditional Formatting with month and 'Dates Occurring'

    I'm not in front of 2007 right now, so unless someone else steps in, this'll have to wait a few hours. No lives depend on it?

  5. #5
    Registered User
    Join Date
    12-14-2008
    Location
    Minneapolis, MN
    Posts
    25

    Re: Conditional Formatting with month and 'Dates Occurring'

    Nope teylyn, no lives depending on it, but if you could take a quick look I would appreciate it.

    I tried the same conditional format in a new spreadsheet and still no luck, perhaps I will have to resort to formulas.

    Thanks again,
    Ray

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Conditional Formatting with month and 'Dates Occurring'

    Yep, I'm seeing the same thing. If you use the conditional formula rules providec by 2007 with Date occurring and choosing Next Month, the year break does not work. So better to resort to formulas where you can actually check what's going on.

    For A1 being the date, set up three rules

    =MONTH(A1)=IF(MONTH(TODAY())=1,13,MONTH(TODAY()))-1
    =MONTH(A1)=MONTH(TODAY())
    =MONTH(A1)=IF(MONTH(TODAY())=12,0,MONTH(TODAY()))+1

    That should cover year breaks bothe sides, i.e. January will see December as the previous month.

    hth

  7. #7
    Registered User
    Join Date
    12-14-2008
    Location
    Minneapolis, MN
    Posts
    25

    Re: Conditional Formatting with month and 'Dates Occurring'

    Hey teylyn,

    Thank you again for the reply. I'm glad that you ran into the same problems that I did; and I'm not crazy.

    Can you take a look at my spreadsheet. I've tried your formulas and can get them close but not perfect. I might be screwing up your A1 date reference. I tried different references but still can't get it to work.

    Thank you,
    Ray
    Attached Files Attached Files

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Conditional Formatting with month and 'Dates Occurring'

    Hi, apply these steps to your first sheet

    - select E6 through to J29
    - open the conditional formatting dialog
    - edit the yellow rule and set it to "use a formula to determine which cells to format"
    - enter this formula:
    =MONTH(E6)=IF(MONTH(TODAY())=1,13,MONTH(TODAY()))-1

    for the blue rule use this formula:

    =AND(E6>0,MONTH(E6)=IF(MONTH(TODAY())=12,0,MONTH(TODAY()))+1)

    Note that the E6 does not have any $ signs, so the cell reference will be relative, i.e. each cell looks at its own date and compares it with today()

    hth

  9. #9
    Registered User
    Join Date
    12-14-2008
    Location
    Minneapolis, MN
    Posts
    25

    Re: Conditional Formatting with month and 'Dates Occurring'

    Perfect. Thank you teylyn, Happy Holidays.

    Ray

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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