+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting on Dates

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    Manchester, England
    MS-Off Ver
    MS 365
    Posts
    52

    Conditional Formatting on Dates

    Hi All
    I have a requirement to format certain rows a different colour depending on the Month element of the date.
    So all of the rows with a date in January are one colour, February a different colour etc.
    The date format is DD:MM:YYYY and the date field itself is in the same column for each entry.
    Does anyone have any ideas on if and how this could be done?
    All help appreciated.
    Thanks
    Chris

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional Formatting on Dates

    Hi,

    Presumably 12 different conditional formatting rules similar to

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where $A1 contains the date and 1 is January, February being 2..etc.

    Apply each rule to the whole range.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    12-18-2016
    Location
    London
    MS-Off Ver
    Microsoft 2013
    Posts
    145

    Re: Conditional Formatting on Dates

    If your request is for conditional formatting by month based on year, I think what you request is a plethora of formula like:

    =and(year(A1)=B$2,month(A1)=1)
    to be repeated with the last 1 changed in integer steps all the way to 12, with a different color for each such value.

  4. #4
    Forum Contributor
    Join Date
    12-18-2016
    Location
    London
    MS-Off Ver
    Microsoft 2013
    Posts
    145

    Re: Conditional Formatting on Dates

    If your request is for conditional formatting by month based on year, I think what you request is a plethora of formula like:

    =and(year(A1)=B$2,month(A1)=1)
    to be repeated with the last 1 changed in integer steps all the way to 12, with a different color for each such value.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multi-Conditional Conditional Formatting and Dates
    By Mousiefuzz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2015, 04:09 AM
  2. Conditional formatting using dates and using dates without years
    By dcef79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 05:35 AM
  3. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  4. [SOLVED] Conditional Formatting Due dates and Completed Dates
    By shansen79 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-30-2014, 01:33 PM
  5. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  6. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  7. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 AM

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