Mar 21 2019 01:35 PM
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
Mar 21 2019 02:24 PM
@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?
Mar 21 2019 02:47 PM
@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
Mar 21 2019 03:02 PM
Mar 21 2019 03:15 PM
Mar 22 2019 09:00 AM
@weknowsafety491519 , I can't download your file. For such sample
formula could be
=IF(C2>1,"Closed",IF(TODAY()>B2,"Expired","Open"))
Mar 25 2019 08:17 AM
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
Mar 25 2019 08:29 AM
Aug 28 2022 10:21 PM
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.no | Name of Firm & Address | License No. | Category | Valid upto | Active/Non active/Cancelled |
1 | abc | 1520 | E | 29-08-2022 | show 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