Home

Formating automatic display of expired, open or closed

%3CLINGO-SUB%20id%3D%22lingo-sub-381367%22%20slang%3D%22en-US%22%3EFormating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-381367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20for%20let%20me%20be%20part%20of%20this%20community.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20issue%20is%20excel%20related.%20I%20do%20not%20kn%20w%20at%20all%20how%20to%20resolve%20or%20to%20figure%20out%20my%20doubt.I%20am%20learning%20english%20and%20if%20you%20see%20any%20mistake%20please%20do%20not%20take%20it%20into%20account.%20I%20would%20like%20that%20my%20excel%20cell%20%22STATUS%22%20shows%20automatically%20the%20words%20%22Expired%2C%20Open%20or%20Closed%22%20dependind%20on%20the%20information%20included%20on%20the%20cell%20%22Deadline%22.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EPlease%2C%20see%20the%20attachement.%20I%20have%20two%20main%20colums%20%22Date%20Assigned%22%2C%20%22DeadLine%22%20and%20%22STATUS%22.%20I%20would%20like%20that%20the%20cells%20under%20the%20column%20%22STatus%22%20shows%20automatically%20the%20words%20%22Expired%2C%20Open%20or%20Closed%22%20dependind%20on%20the%20information%20included%20on%20the%20cell%20%22Deadline%22.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance%20for%20you%20support%20on%20this%20issue.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EJhon%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-381367%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386801%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Juan%2C%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305684%22%20target%3D%22_blank%22%3E%40weknowsafety491519%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386784%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386784%22%20slang%3D%22en-US%22%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20on%20this%20matter.%20Youhave%20been%20VERY%20USEFUL%20AND%20KIND%20helping%20me%20with%20this%20issue.%20Sorry%20that%20my%20file%20did%20not%20open.%20I%20used%20the%20formula%20and%20works%20PERFECTLY.%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20once%20again%2C%3C%2FP%3E%3CP%3EBless%20you%20and%20have%20a%20great%20week.%3C%2FP%3E%3CP%3EJuan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-382729%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-382729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305684%22%20target%3D%22_blank%22%3E%40weknowsafety491519%3C%2FA%3E%26nbsp%3B%2C%20I%20can't%20download%20your%20file.%20For%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20565px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97174iB52BEBF6CEB9474B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(C2%26gt%3B1%2C%22Closed%22%2CIF(TODAY()%26gt%3BB2%2C%22Expired%22%2C%22Open%22))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-381675%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-381675%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attaching%20the%20file%2C%20maybe%20I%20attached%20the%20wrong%20one%2C%20sorry.%3C%2FP%3E%3CP%3EJhon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-381662%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-381662%22%20slang%3D%22en-US%22%3EThank%20you.%20So%2C%20you%20have%20one%20more%20column%20Date%20Completed%2C%20correct%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-381528%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-381528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20request.%3C%2FP%3E%3CP%3EYes%2C%20I%20will%20do%20my%20best%20to%20%3CSPAN%3Eclarify%20my%20logic%20a%20bit.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20including%20date%20that%20requires%20a%20date%20when%20the%20event%20is%20assigned%20to%20a%20person%2C%20at%20the%20same%20time%20a%20deadline%20is%20assigned%20to%20this%20issue%20and%20person%20to%20close%20the%20item.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20would%20like%20that%20the%20cells%20unders%20%22Status%22%20shows%20%22expired%22%2C%20or%20%22Open%22%20or%20%22closed%22%20when%20I%20open%20the%20file.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20meant%2C%20let%20say%2C%20we%20have%20assigne%20a%20responsable%2C%20a%20date%20when%20the%20responsibility%20was%20assigned%20and%20the%20deadline%20when%20the%20issue%20must%20be%20done.%20If%20the%20item%20was%20assigned%20on%20March%2020%202019%20and%20the%20Deadline%20has%20been%20set%20up%20for%20March%2031%202019%20and%20I%20open%20the%20file%20on%20April%201%202019%20and%20the%20item%20have%20not%20been%20done%20them%20in%20the%20cell%20%22Status%22%20shall%20say%20%22EXPIRED%22%3B%20or%20if%20I%20open%20the%20file%20on%20March%2028%202019%20and%20the%20item%20have%20not%20been%20defined%20a%20date%20of%20complition%20then%20the%20status%20must%20say%20%22Open%22%2C%20or%20if%20when%20I%20open%20the%20file%20and%20the%20item%20hve%20been%20complete%20on%20March%2022%20then%20the%20status%20shall%20say%20%22Closed%22.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDate%20Assigned%20that's%20when%20I%20opened%20the%20task.%20(YES)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20task%20is%20Expired%20if%20Deadline%20%26lt%3B%20DEADLINE%20defined.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20we%20before%20deadline%20it'll%20be%20always%20Opened%3F%20(YES)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat%20is%20the%20trigger%20for%20Opened%2FClosed%3F%20Opened%20if%20deadline%20is%20%26lt%3B%20or%20%3D%20to%20the%20deadline%20when%20I%20open%20the%20file.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EClosed%20when%20the%20date%20completed%20is%20defined.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20if%20Date%20Assigned%20is%20blank%2C%20what%20shall%20be%20the%20Status%20for%20the%20task%3F%20The%20date%20assigned%20will%20be%20always%20defined.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20I%20could%20explain%20it.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EJhon%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-381437%22%20slang%3D%22en-US%22%3ERe%3A%20Formating%20automatic%20display%20of%20expired%2C%20open%20or%20closed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-381437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305684%22%20target%3D%22_blank%22%3E%40weknowsafety491519%3C%2FA%3E%26nbsp%3B%2C%20could%20you%20please%20clarify%20your%20logic%20a%20bit.%20Date%20Assigned%20that's%20when%20you%20opened%20the%20task.%20If%20exists%2C%20when%20the%20task%20is%20Expired%20if%20Deadline%20%26lt%3B%20TODAY().%20If%20we%20before%20deadline%20it'll%20be%20always%20Opened%3F%20What%20is%20the%20trigger%20for%20Opened%2FClosed%3F%20And%20if%20Date%20Assigned%20is%20blank%2C%20what%20shall%20be%20the%20Status%20for%20the%20task%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
weknowsafety491519
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

7 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 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies