Home

What IF formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-462841%22%20slang%3D%22en-US%22%3EWhat%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462841%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20set%20up%20a%20formula%20that%20calculates%20a%20date%20based%20on%20a%20cells%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EName%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Date%20Reviewed%20%26nbsp%3B%20%26nbsp%3B%20Rating%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Date%20of%20Next%20Review%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3EJ.%20Smith%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20April%2015%202019%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Good%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20(6%20months%20from%20april%2015%202019)%3C%2FP%3E%3CP%3EP.%20Smith%20%26nbsp%3B%20%26nbsp%3B%20April%2015%202019%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20Poor%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20(3%20months%20from%20april%2015%202019)%3C%2FP%3E%3CP%3ES.%20Smith%20%26nbsp%3B%20%26nbsp%3B%20April%2015%202019%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20New%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%3CSPAN%3E(3%20months%20from%20april%2015%202019)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20set%20up%20a%20formula%20that%20based%20on%20a%20%22Good%22%2C%20%22Poor%22%2C%20or%20%22New%22%20value%2C%20it%20generates%20a%20date%20that%20is%20either%203%20months%20from%20the%20previous%20date%20or%206%20months%20from%20the%20previous%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-462841%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462922%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322210%22%20target%3D%22_blank%22%3E%40Vanessa_Aceti%3C%2FA%3E%20%2C%20that%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DEOMONTH(B2%2CIF(C2%3D%22Good%22%2C6%2C3))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462956%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462956%22%20slang%3D%22en-US%22%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%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20like%20the%20generated%20date%20to%20be%20based%20on%20a%20result%20in%20the%20%22Rating%22%20column.%20Not%20the%20previous%20date.%20The%20previous%20date%20is%20a%20starting%20point%20for%20the%20generated%20future%20date.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20that%20makes%20sense.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-462981%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-462981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322210%22%20target%3D%22_blank%22%3E%40Vanessa_Aceti%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20the%20formula%20do%20it%20takes%20previous%20date%20and%20adds%20to%20it%206%20months%20if%20the%20rating%20is%20Good%20or%203%20month%20otherwise.%20What%20do%20you%20mean%20under%20%22%3CSPAN%20style%3D%22display%3A%20inline%20!important%3B%20float%3A%20none%3B%20background-color%3A%20%23ffffff%3B%20color%3A%20%23333333%3B%20font-family%3A%20'SegoeUI'%2C'Lato'%2C'Helvetica%20Neue'%2CHelvetica%2CArial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20line-height%3A%201.7142%3B%20orphans%3A%202%3B%20overflow-wrap%3A%20break-word%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3ENot%20the%20previous%20date%3C%2FSPAN%3E%22%20-%20we%20add%20months%20to%20TODAY%20or%20what%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-464258%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-464258%22%20slang%3D%22en-US%22%3EThe%20formula%20in%20D2%2C%20copied%20down%20rows%2C%20is%3A%3CBR%20%2F%3E%3DEDATE(B2%2C%3CBR%20%2F%3E(C2%3D%22Good%22)*3%2B3)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467669%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467669%22%20slang%3D%22en-US%22%3EThe%20phrase%20%E2%80%9Cfrom%20the%20previous%20date%E2%80%9D%20denotes%20that%20EDATE%20should%20be%20used%20instead%20of%20EOMONTH%2C%20which%20indicates%20%E2%80%9Cfrom%20the%20end%20of%20the%20month%20wherein%20the%20previous%20date%20pertains%E2%80%9D.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467730%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20sure%2C%20that%20shall%20be%20EDATE%20and%20sorry%20for%20the%20misprint%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467911%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467911%22%20slang%3D%22en-US%22%3EIt%20is%20now%20manifest%20that%20we%20intended%20to%20suggest%20the%20same%20formula.%20I%20just%20avoided%20IF%20by%20using%20Boolean%20logic%2C%20which%20I%20noticed%20was%20your%20favorite.%20Incidentally%2C%20you%20might%20have%20unwittingly%20resorted%20to%20the%20plain%20IF%2C%20rather%20than%20the%20cryptic%20Boolean%20logic.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-467947%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-467947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20%2C%20yes%2C%20I%20personally%20prefer%20*%20and%20%2B%20instead%20of%20AND%20and%20OR%2C%20but%20I%20don't%20push%20other%20people%20to%20do%20the%20same%20-%20depends%20on%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468009%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468009%22%20slang%3D%22en-US%22%3EWe%20share%20the%20same%20preference%20to%20Boolean%20logic%2C%20although%20it%20may%20seem%20cryptic%20to%20the%20uninitiated.%20I%20always%20use%20it%2C%20regardless%20of%20the%20preference%20of%20others%2C%20but%20I%20must%20admit%20that%20I%20sometimes%20inadvertently%20use%20the%20instantly%20conceivable%20IF%2C%20AND%2C%20and%20OR.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468492%22%20slang%3D%22en-US%22%3ERe%3A%20What%20IF%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468492%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20the%20same%20story%20as%20to%20use%20only%20shortcuts%20or%20only%20mouse%3B%20use%20tables%20or%20avoid%20tables%3B%20use%20VLOOKUP%20or%20INDEX%2FMATCH%2C%20etc.%20Doesn't%20matter%2C%20all%20works.%20Depends%20on%20personality%20and%20concrete%20situations.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Vanessa_Aceti
New Contributor

Good afternoon,

 

I am trying to set up a formula that calculates a date based on a cells value.

 

For Example:

 

Name        Date Reviewed     Rating           Date of Next Review         

J. Smith      April 15 2019         Good         (6 months from april 15 2019)

P. Smith     April 15 2019          Poor          (3 months from april 15 2019)

S. Smith     April 15 2019          New          (3 months from april 15 2019)

 

I would like to set up a formula that based on a "Good", "Poor", or "New" value, it generates a date that is either 3 months from the previous date or 6 months from the previous date.

 

Is this possible?

 

 

10 Replies

@Vanessa_Aceti , that could be like

=EOMONTH(B2,IF(C2="Good",6,3))

 

Highlighted
@Sergei Baklan

I'd like the generated date to be based on a result in the "Rating" column. Not the previous date. The previous date is a starting point for the generated future date.

Hope that makes sense.

@Vanessa_Aceti ,

 

What the formula do it takes previous date and adds to it 6 months if the rating is Good or 3 month otherwise. What do you mean under "Not the previous date" - we add months to TODAY or what?

The formula in D2, copied down rows, is:
=EDATE(B2,
(C2="Good")*3+3)
The phrase “from the previous date” denotes that EDATE should be used instead of EOMONTH, which indicates “from the end of the month wherein the previous date pertains”.

@Twifoo , sure, that shall be EDATE and sorry for the misprint

It is now manifest that we intended to suggest the same formula. I just avoided IF by using Boolean logic, which I noticed was your favorite. Incidentally, you might have unwittingly resorted to the plain IF, rather than the cryptic Boolean logic.

@Twifoo , yes, I personally prefer * and + instead of AND and OR, but I don't push other people to do the same - depends on situation.

We share the same preference to Boolean logic, although it may seem cryptic to the uninitiated. I always use it, regardless of the preference of others, but I must admit that I sometimes inadvertently use the instantly conceivable IF, AND, and OR.

That's the same story as to use only shortcuts or only mouse; use tables or avoid tables; use VLOOKUP or INDEX/MATCH, etc. Doesn't matter, all works. Depends on personality and concrete situations.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies