+ Reply to Thread
Results 1 to 13 of 13

Conditional Formatting for Dates

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Conditional Formatting for Dates

    Hi all,
    I've spent a long time searching for an answer to my query but nothing has seemed to work for me.

    Here is my dilemma;
    I have a spreadsheet for tracking shooting records for my cadets squadron, and there are Weapons Handling Tests which expire every 6 months from the date cadets pass it which is entered in the spreadsheet. I've managed to get a warning for a month away and a week away, but there is no way that I can find to tell me if they are already out of date after 6 months or longer. Ideally I'd want a red fill when 6 months or longer has passed.

    I'm new to this sort of formatting and I might end up needing it "dumbed" down quite a bit. I'm using Excel 2010 FYI.

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Conditional Formatting for Dates

    =(TODAY() < DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)))
    and format as Red

    where A1 is the pass date
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    Thanks for the quick reply!

    However the ways that I've been trying to go about that formatting isn't working in the slightest. Would you be able to have a look at it and remedy the situation, it would be much appreciated.

    ThanksShooting.xlsx

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Conditional Formatting for Dates

    Hi Ryanstaple

    Have a look at the attached.

    Conditions:

    1/ 1 Week
    2/ 1 Month (31 days)
    3/ 6+ months out of date.
    Attached Files Attached Files
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    Thanks for that, but it only seems to be working for the cell that is filled red and not for the other cells, also the red date needs to be the in the column of the .22 WHT?/L98 WHT?/L81 WHT? instead of the restest column. Apologies for being a pain, I don't understand this formatting malarkey.

    Regards
    Ryan

  6. #6
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Conditional Formatting for Dates

    Why are they not working! Look at the dates in C9,E9 & G9

  7. #7
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    Those are working correctly, it's the one in C27 that won't work with another cell and should be in the cell to its left.

  8. #8
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Conditional Formatting for Dates

    See if this is OK!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    It's in the right cell but the cell isn't going red when it is 6 months out of date, it seems to have to be more than 6 months. For example type 04/08/12 into a blank cell on the .22 WHT? column and it should go red because it is 6 months out of date, however it isn't...

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Conditional Formatting for Dates

    The problem was, that i did not understand post #1
    So once it goes past today, that is when you want the cell to turn Red & not 6 months down the line, is that correct!

  11. #11
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    Apologies for confusion, I may have stated the wrong thing. Once the date entered in the "WHT?" column becomes 6 months ago today it should fill red.

  12. #12
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Conditional Formatting for Dates

    If you select the Column B . Then on the Home Tab > Conditional Formatting > Manage Rules > Click on the Rule > Edit Rule. You should see the formula for the rule. I have deleted the "-182". That means once the date in the cell next to it is less then today, the cell in column B is Red, would that be correct!

    See the attached, also select the other columns and you will see the conditions.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-06-2013
    Location
    Taunton, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Conditional Formatting for Dates

    Happy days! Thank you very much I am extremely grateful! You have helped a massive amount and I apologise for not making it clear at the start. I know where to come next time something goes wrong!

    Regards
    Ryan Staple

+ 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