+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting based on date

  1. #1
    Registered User
    Join Date
    05-22-2020
    Location
    Taunton, England
    MS-Off Ver
    O365
    Posts
    1

    Question Conditional formatting based on date

    Hi All

    This is my first post :-)

    I have looked at a lot of help online and there seems to be a everything but what I'm looking for! I have an output from a log file with a date & time column (21/04/2020 09:28:18) and what I would like to achieve is to alternate the fill colour of each group of rows between two contrasting shades so that it is obvious to the viewer when the date changes. So the colour change only occurs when the date changes and there is a varying number of entries (rows) for each date.

    The closest thing I can get is a formula for finding fractions of a year (is there a day equivalent?), INT is used to get a whole number. The number is then put in a "helper" column and conditional formatting that changes the fill colour of the row is applied based on the "helper" column


    I hope I've made myself clear?
    Thank you for any help you can give

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,830

    Re: Conditional formatting based on date

    you can use this formula

    =MOD(SUMPRODUCT(--(INT($A$2:$A2)<>INT($A$1:$A1))),2)

    in conditional format rules
    use
    =MOD(SUMPRODUCT(--(INT($A$2:$A2)<>INT($A$1:$A1))),2)=1
    then a fill colour
    and if needing a different colour
    =MOD(SUMPRODUCT(--(INT($A$2:$A2)<>INT($A$1:$A1))),2) = 0

    see the example of the formula in column G
    Not needed , but shows what it is doing
    then look at the conditional formatting i applied
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ 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] Conditional Formatting Based on Date Above
    By slippy47 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2019, 03:08 PM
  2. [SOLVED] Conditional Formatting for Gant Chart based on : Start Date / End Date / Status
    By Stancur in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2018, 04:13 AM
  3. Conditional Formatting Based on Date
    By ashdougx in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-15-2017, 04:56 PM
  4. [SOLVED] conditional formatting based on date
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-21-2016, 03:07 PM
  5. Conditional Formatting based on date
    By sans87 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-14-2013, 05:30 PM
  6. Replies: 3
    Last Post: 11-23-2013, 06:21 AM
  7. Conditional Formatting Based on Date
    By krie2509 in forum Excel General
    Replies: 3
    Last Post: 02-23-2010, 11:09 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