Generating current date in calculated field when conditions are met/hiding it when conditions are not met RRS feed

  • Question

  • I am working in SharePoint 2007 and have a requirement to generate the current date in a column called "Stage one start"if two conditions are met, but hide this column when they are not. For example, I have three columns: 

    Stage one start (calculated column)

    Current phase (choice column)

    Opportunity status (choice column)

    Requirement: If Current phase= 1 and Opportunity status = decision-ready, then Stage one start = current date

    If Current phase = 1 and Opportunity status does not = decision-ready, then hide Stage one start

    I have been trying to do this with a calculated column, but keep getting a syntax error. I am new to calculated columns and formulas, so I am not sure if I have the formula correct or even if you can accomplish this task using calculated columns. I created an additional date/time column (I named it "Today's date") that has a default value of "Today's date." I then added the following to the formula box of  the Stage one start column:

    =IF(AND[Current phase]=1, [Opportunity status]=decision-ready), [Today's date]

    Does anyone have any ideas? If this needs to be done in Designer or Infopath, could you provide me with a link to instructions or offer me some instruction on how I can do this?



    Monday, November 18, 2013 7:35 PM

All replies

  • Hi

    generally speaking

    IF formula is

    IF ( condition, value if condition is TRUE, value if condition is FALSE )

    If you need AND operator, should looks like

    =IF(AND(Column1>Column2, Column1<Column3), "OK", "Not OK")

    in your case

    =IF(AND([Current phase]="1", [Opportunity status]="decision-ready"), [Today's date],"NOK")

    More others examples, here

    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Monday, November 18, 2013 8:26 PM
  • Thanks for the reply. Unfortunately, I still get the error, "The formula contains a syntax error or is not supported." I will review the other examples to see if I can figure it out.
    Tuesday, November 19, 2013 1:10 AM