+ Reply to Thread
Results 1 to 13 of 13

Conditional Formating not working

  1. #1
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Conditional Formating not working

    Hi, I'm new to excel and I'm struggling with Conditional Formatting.

    I have a spreadsheet that has names of peoples and the dates they attended a certain course - I've managed to get a formula which then calculates the date when their refresher course is due and that works fine.

    What I wanted to do next was colour code the date that was generated as follows:
    Date passed: Red
    Date within the next 12 months: Yellow
    Date more than 12 months away: Green

    I created Conditional Formatting that I thought was working however if a date where the year is 2022 (so more than 12 months so should be green) is generated it is yellow rather than green?

    I have the following:
    RED: =AND($AT22<>"",$AT22<=TODAY())
    YELLOW: =AND($AT22<>"",DATEDIF(TODAY(),$AT22,"Y")<=1,$AT22>TODAY())
    GREEN: =AND($AT22<>"",DATEDIF(TODAY(),$AT22,"Y")>1)

    Can anyone see what I have done wrong or why it's not working as expected please? Any help would be greatly appreciated

    Thanks
    Attached Files Attached Files
    Last edited by AB4122; 11-11-2020 at 07:16 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Conditional Formating not working

    Post a sample workbook please. See the yellow banner.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    Apologies - it had personal information which was why I didn't want to post but I have now deleted and will post

    Thanks

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Conditional Formating not working

    When you use the DateDif formula with the "Y" Unit, it rounds down so essentially it would have to be at least 2 years in the future to be Green.

  5. #5
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    Is there anyway of making it so that it would work?

    Also I've now managed to add a check box at the start so that if a member of staff is OTR (Off the run) I can highlights their row - however I am struggling to get the right formula so that R9 shows the count only for those left on the run?

    So for example
    - there are 33 members of staff listed on the spreadsheet (30 firefighters and 3 Station Managers)
    - So if FF B was Off The Run I would uncheck the box and that would highlight his row (I've managed to do this bit)
    - I want to count the number of qualified people in column R (Phase 1) so I need to count R19 to R85 but only if there is a date in the box and if B19 to B85 is 'TRUE'

    I managed to create one formula but it counted the 3 SMs even though their date in column R was blank and then another formulas I try either result in an error or I get #SPILL! in R9 and R13.

    Any help would be greatly appreciated.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Conditional Formating not working

    If I understand correctly, try this in cell R9:
    =COUNTIFS($B19:$B100,"<>"""",R19:R100,"<>"""")

    Does this help?

  7. #7
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    Thanks for the reply and help

    It nearly works - the formula you gave does alter the count total in R9 if I untick the check box and take them Off The Run, the problem is it is still counting blank cells too? So it is counting the Station Managers in rows 81, 83, 85 as having the Phase 1 course which they haven't.

    I'm new to excel but from what I have read it seems like I need a COUNTA formula which would count only cells that have an input combined with a IFS formula for whether the box is checked - it's almost like I need a COUNTAIFS but that's not a real formula is it? Is there a way of combining the two?

    Thanks again for the help.

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditional Formating not working

    Try:
    Please Login or Register  to view this content.
    Quang PT

  9. #9
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    Thanks - that gives a result of 0 (is that because Excel doesn't recognise the date as >1 ?
    If I changed the 1 to > and then put a formula to make the year greater than the earliest year? (courses will never be backdated earlier so this value will never change)

    No idea what this bit of formula would be though?

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditional Formating not working

    It works for me.

    ">1" is enough for range with date and blank cells
    Anyway, try ">40000" (6 Jul 2009), or 20000 or 10000, to see if it works.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    I changed it slightly from
    =COUNTIFS($B$19:$B$100,TRUE,$R19:$R100,">1")
    to
    =COUNTIFS($B$19:$B$100,TRUE,$R$19:$R$100,">1")
    and it works!!! Thank you so much!!!

    Next problem I need to solve...
    1. Is actually the problem on my original post - I still don't know if there is a way to make dates that are past todays date:red, todays date - one year:yellow, over one year away:green
    2. On the count formula that has just been solved - is there also a way to add to the formula so that if a date is red, (out of date), it's taken off the count?

    Then that will be the spreadsheet finished (I hope!)

    Thanks again for all the help

    Andy

  12. #12
    Registered User
    Join Date
    11-11-2020
    Location
    Newcastle, England
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Conditional Formating not working

    Thanks for all the help so far - so close to getting it where it needs to be now. I've attached the updated spreadsheet and need to solve two more problems/formulas.


    So the formula that I'm struggling with next is for cells AN10, AT10 and AX10 and then ultimately I'll need to change AJ16, AR16 and AV16 - Once I get one I should be able to work out the others so I will just take AN10 as an example:
    • AN10 needs to basically count how many in AN from cells 22 to 80 are red (past todays date and need re-training) - this needs to be whether the row is selected or not with the 'tick box'
    • AJ16 then needs to be ((AJ12 - technicians whos names are ticked but who are out of date) - AJ14)
    Does this make sense? Is it even possible?

    Secondly I still can't work out conditional formatting that would make dates:
    Today or past: Red
    Tomorrow - 1 year: Yellow
    Over 1 year away: Green

    The formatting I have at the moment seems to work for red but for the yellow if I put todays date in the column before (AR for example) it adds 3 years and goes green(AT), if I put yesterdays date adds 3 years but it turns yellow (AT) ? Someone mentions it's because "DateDif formula with the "Y" Unit, it rounds down" - would I be better using a TODAY formula or is that possible?

    Thanks for the help
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Conditional Formating not working

    OK - this is now a new problem, so start a new thread with a MUCH better (more precise) thread title.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Conditional formating not working
    By JukeBoxHero in forum Excel General
    Replies: 2
    Last Post: 03-26-2015, 06:50 PM
  2. Replies: 5
    Last Post: 12-08-2014, 06:55 PM
  3. Conditional formating non working days
    By Mangrove in forum Excel General
    Replies: 1
    Last Post: 06-05-2013, 09:58 PM
  4. Conditional formating not working in VBExcel
    By apia in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-09-2012, 03:25 AM
  5. conditional formating not working
    By tsatsos007 in forum Excel General
    Replies: 5
    Last Post: 12-30-2010, 09:45 AM
  6. Conditional formating not working
    By ujjawal in forum Excel General
    Replies: 1
    Last Post: 07-27-2009, 01:39 PM
  7. Conditional formating not working on #Value!
    By feejo in forum Excel General
    Replies: 1
    Last Post: 03-05-2008, 05:27 PM

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