Forum Discussion

weknowsafety491519's avatar
weknowsafety491519
Copper Contributor
Mar 21, 2019

Formating automatic display of expired, open or closed

Hello,

Thank you for let me be part of this community.

My issue is excel related. I do not kn w at all how to resolve or to figure out my doubt.I am learning english and if you see any mistake please do not take it into account. I would like that my excel cell "STATUS" shows automatically the words "Expired, Open or Closed" dependind on the information included on the cell "Deadline". 
Please, see the attachement. I have two main colums "Date Assigned", "DeadLine" and "STATUS". I would like that the cells under the column "STatus" shows automatically the words "Expired, Open or Closed" dependind on the information included on the cell "Deadline". 

Thanks in advance for you support on this issue.
Jhon

Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 21, 2019

    weknowsafety491519 , could you please clarify your logic a bit. Date Assigned that's when you opened the task. If exists, when the task is Expired if Deadline < TODAY(). If we before deadline it'll be always Opened? What is the trigger for Opened/Closed? And if Date Assigned is blank, what shall be the Status for the task?

    • weknowsafety491519's avatar
      weknowsafety491519
      Copper Contributor
      Mar 21, 2019

      SergeiBaklan Thank you for your request.

      Yes, I will do my best to clarify my logic a bit.

      I am including date that requires a date when the event is assigned to a person, at the same time a deadline is assigned to this issue and person to close the item.

      I would like that the cells unders "Status" shows "expired", or "Open" or "closed" when I open the file.

      I meant, let say, we have assigne a responsable, a date when the responsibility was assigned and the deadline when the issue must be done. If the item was assigned on March 20 2019 and the Deadline has been set up for March 31 2019 and I open the file on April 1 2019 and the item have not been done them in the cell "Status" shall say "EXPIRED"; or if I open the file on March 28 2019 and the item have not been defined a date of complition then the status must say "Open", or if when I open the file and the item hve been complete on March 22 then the status shall say "Closed".

       

      Date Assigned that's when I opened the task. (YES)

      The task is Expired if Deadline < DEADLINE defined.

      If we before deadline it'll be always Opened? (YES)

      What is the trigger for Opened/Closed? Opened if deadline is < or = to the deadline when I open the file.

      Closed when the date completed is defined.

      And if Date Assigned is blank, what shall be the Status for the task? The date assigned will be always defined.

       

      I hope I could explain it.

      Thanks

      Jhon

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Mar 21, 2019
        Thank you. So, you have one more column Date Completed, correct?

Resources