Forum Discussion

Daveyboy1981's avatar
Daveyboy1981
Copper Contributor
Jul 15, 2020

Conditional Formatting for dates

I have a spreadsheet with names going down on the left side column, at the top going right in the columns I have courses people have taken. In each intersecting cell there is the date of expiry for the course. Each course has a 3 year expiry, and everyone takes these courses on different dates. I would like to know if it is possible to use conditional formatting to have valid tickets green, change colors to alert me 2 months prior to expiry, and then turn red when they expire. I have watched a few videos, but they all seem to have dates in the same column. Please help

excel

8 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jul 15, 2020
    examble for Contitional Formating

    1) =$I$6<=TODAY() +60 Explain : I6 = Expiry Date , TODAY Date is automaticaly from systen generate, +60 Explain: is the 2 months option for info before expire.
    2) =$I$6<=TODAY() Explain: the same end date if is expire
    3) =$I$6>=TODAY() Explain: if the ticket not are expired

    I would be happy to find out if I could help.

    Nikolino
    I know I don't know anything (Socrates)
    • Daveyboy1981's avatar
      Daveyboy1981
      Copper Contributor
      Jul 15, 2020

      NikolinoDE 

      So I have made an example of what I have. The dates in the boxes are expiry dates. Should i have the dates in there for when the ticket was acquired? I would like to have all formulas the same and green from date acquired, yellow when they reach 2 years 10 months and red on expiry.

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Jul 15, 2020

        Daveyboy1981 

         

        a small example in the file ... i am not sure if that is what you are looking for ... because the translation in german is not the best ... if not that what you are looking for, please give me a short message ...
        if this is what you are looking for, please give me short message with a thumbs up 🙂
         
         

        anyway i would be happy to find out if I could help.

         

        Nikolino

        I know I don't know anything (Socrates)

         

    • Daveyboy1981's avatar
      Daveyboy1981
      Copper Contributor
      Jul 15, 2020

      NikolinoDE 

      NAMEConfined Space Entry/MonitorCrane and RiggingEWPFall ProFire Extinguisher SafetyFirst AidForkliftHealth and Safety Committee RepsSite Specific WHMIS 2015
      Steve April 16, 2022February 12, 2023February 11, 2023 May 17, 2022April 3, 2020 October 28, 2019
      Chris July 7, 2020November 20, 2023November 19, 2022     
      Wayne         
      Arthur July 7, 2020      April 23, 2020
      Wade April 16, 2022October 27, 2020October 6, 2020 July 12, 2021March 6, 2022  
      Dylan April 16, 2022      September 23, 2019
      Judy         
      Susan March 11, 2022       
      Jarrod July 7, 2020   October 24, 2020  September 23, 2019
      MarkSeptember 19, 2020 April 2, 2021September 18, 2020     
      Leslie April 16, 2022   February 26, 2023March 5, 2022April 17, 2020

      April 23, 2020

       

Resources