Formating automatic display of expired, open or closed

New Contributor

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

8 Replies

@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?

@Sergei Baklan 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

Thank you. So, you have one more column Date Completed, correct?

Yes, @Sergei Baklan 

I am attaching the file, maybe I attached the wrong one, sorry.

Jhon

@weknowsafety491519 , I can't download your file. For such sample

image.png

formula could be

=IF(C2>1,"Closed",IF(TODAY()>B2,"Expired","Open"))

Dear @Sergei Baklan 

Thank you very much for your help on this matter. Youhave been VERY USEFUL AND KIND helping me with this issue. Sorry that my file did not open. I used the formula and works PERFECTLY. 

thanks once again,

Bless you and have a great week.

Juan

Hi Juan,

You are welcome, glad to help

@weknowsafety491519 

@Sergei Baklan 

 

Hi sir,

 I have an issue in my office work. I need formula for excel sheet. I have an excel sheet containing electrical License data.  tabel containing the following label

 

S.noName of Firm & AddressLicense No.CategoryValid uptoActive/Non active/Cancelled
1abc1520E29-08-2022show active/Non active/ cancelled

Note: one month grace period also allowed to contractor, after the end of one month grace period, license will be non active. and after two years license will be cancelled.

 

 

Kindly sir put up formula to automatically show active/non active/cancelled thanks