Home

Excel If or formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-667113%22%20slang%3D%22en-US%22%3EExcel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667113%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20formula%20which%20looks%20at%20a%20date%20in%20column%20B%20and%20returns%20how%20many%20days%20from%20that%20date%20-%20thi%20s%20formula%20is%26nbsp%3B%20%3DToday()-B3%2C%20I%20have%20another%20column%20with%20Text%20which%20states%20Major%20Overdue%20or%20other%20text.%26nbsp%3B%20I%20would%20like%20to%20change%20my%20formula%20that%20is%20%3DToday()-B3%20so%20that%20it%20looks%20at%20the%20B%20column%20and%20at%20the%20text%20column.%26nbsp%3B%20I%20would%20like%20it%20to%20return%20the%20number%20of%20days%20since%20the%20date%20in%20B%20column%20as%20it%20currently%20does%2C%20unless%20it%20states%20Major%20Overdue%20in%20the%20text%20column%2C%20in%20which%20case%20it%20would%20put%20in%2030%20-%20is%20this%20possible%20and%20does%20this%20make%20sense%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-667113%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667222%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354261%22%20target%3D%22_blank%22%3E%40SPearson1973%3C%2FA%3E%26nbsp%3B%2C%20if%20your%20text%20is%20in%20C3%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3D(30-TODAY()%2BB3)*(C3%3D%22Major%20Overdue%22)%2BTODAY()-B3%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%3E%3DIF(C3%3D%22Major%20Overdue%22%2C30%2CTODAY()-B3)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667265%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667265%22%20slang%3D%22en-US%22%3EYou%20may%20also%20CHOOSE%20this%20formula%3A%3CBR%20%2F%3E%3DCHOOSE((C3%3D%E2%80%9CMajor%20Overdue%E2%80%9D)%2B1%2C%3CBR%20%2F%3ETODAY()-B3%2C%3CBR%20%2F%3E30)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-667272%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-667272%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%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%2C%20can%20i%20add%20an%20or%20to%20this%20so%20if%20C3%20is%20Major%20Overdue%20or%20Overdue%20to%20make%20it%2030%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-668731%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354261%22%20target%3D%22_blank%22%3E%40SPearson1973%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20sure%2C%20you%20may%20wrap%20both%20options%20with%20OR%2C%20alternatively%20use%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DIF(%20ISNUMBER(SEARCH(%22Overdue%22%2CC3))%2C%2030%2C%20TODAY()-B3)%3C%2FPRE%3E%0A%3CP%3ECondition%20returns%20TRUE%20if%20the%20word%26nbsp%3B%3CSPAN%3EOverdue%20will%20be%20within%20any%20text%20in%20C3.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESame%20with%20the%20other%20formulas%20above.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-668745%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20or%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668745%22%20slang%3D%22en-US%22%3EOR%2C%20you%20may%20CHOOSE%20this%20formula%3A%3CBR%20%2F%3E%3DCHOOSE(OR(C3%3D%7B%E2%80%9CMajor%20Overdue%E2%80%9D%2C%E2%80%9DOverdue%E2%80%9D%7D)%2B1%2C%3CBR%20%2F%3ETODAY()-B3%2C%3CBR%20%2F%3E30)%3C%2FLINGO-BODY%3E
SPearson1973
New Contributor

I have a formula which looks at a date in column B and returns how many days from that date - thi s formula is  =Today()-B3, I have another column with Text which states Major Overdue or other text.  I would like to change my formula that is =Today()-B3 so that it looks at the B column and at the text column.  I would like it to return the number of days since the date in B column as it currently does, unless it states Major Overdue in the text column, in which case it would put in 30 - is this possible and does this make sense

5 Replies

@SPearson1973 , if your text is in C3 that could be

=(30-TODAY()+B3)*(C3="Major Overdue")+TODAY()-B3

or

=IF(C3="Major Overdue",30,TODAY()-B3)

 

You may also CHOOSE this formula:
=CHOOSE((C3=“Major Overdue”)+1,
TODAY()-B3,
30)

@Sergei Baklan 

Thank you so much, can i add an or to this so if C3 is Major Overdue or Overdue to make it 30?

@SPearson1973 , yes, sure, you may wrap both options with OR, alternatively use 

=IF( ISNUMBER(SEARCH("Overdue",C3)), 30, TODAY()-B3)

Condition returns TRUE if the word Overdue will be within any text in C3.

Same with the other formulas above.

 

OR, you may CHOOSE this formula:
=CHOOSE(OR(C3={“Major Overdue”,”Overdue”})+1,
TODAY()-B3,
30)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 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
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies