1

I'm trying to add more than one if's (also) statements inside a formula, but it is not working because of a syntax error. What I'm trying to do is if actual sign date column is less or equal to the expected date column (both date fields), to change the color in green, else to be blue-ish.

Also I'm trying to add into that same formula if the column named 'Confirmation Read And Agreed' is blank (empty) and the expected sign date is less than the date column set to today's date 'Today' (basically if expected sign date is overdue), to bring me a red color on that column. Do please have a look at the formula below.

=IF([Actual Sign Date]<=[Expected Sign Date]
    ,"<div style= 'background-color:#00CC00;'>"
        &[Actual Sign Date]
        &"</div>"
    ,"<div style= 'background-color:#00ffff;'>"
        &[Expected Sign Date]
        &"</div>"
    ,IF(ISBLANK[Confirmation Read And Agreed]
           &&
              [Expected Sign Date]<[Today]
       ,"<div style= 'background-color:#FF0000;'>"
           &[Confirmation Read And Agreed]
           &"</div>"
       )
  )
Improve this question
0

1 Answer 1

Reset to default
0

Today in a Formula is a never ending story: How to use Today and Me in Calculated column

I reformatted your Formula with line breaks.

2 errors I see at first glance:

  • First IF is not closed with ) so you add a fourth parameter

  • SharePoint does not do an && operator, you need an AND function

See: http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List

Tip: always edit Formulas in a decent Text Editor, SharePoint will remove the linebreaks on copy/paste/save

The FunctionList page also helps you to a Bookmarklet to aid with editing:

Improve this answer
5
  • Hi Danny!I edited the code like you said, but it still doesn't accept it due to a syntax error.Here's the edited code, any ideas how to write the following in a way, so it could be accepted=IF([Actual Sign Date]<=[Expected Sign Date],"<div style= 'background-color:#00CC00;'>"&[Actual Sign Date]&"</div>","<div style= 'background-color:#00ffff;'>"&[Expected Sign Date]&"</div>",IF(ISBLANK([Confirmation Read And Agreed])AND[Expected Sign Date]<[Today],"<div style= 'background-color:#FF0000;'>"&[Confirmation Read And Agreed]&"</div>"))
    – Benny Blanco
    Dec 7, 2016 at 8:19
  • Then trim it down, and build it step by step
    – Danny '365CSI' Engelman
    Dec 7, 2016 at 8:22
  • OK Danny, thank you very much! Made what I was asking for and wanted, but the easier and not as complicated way with JS and JQuery. Have a great day!
    – Benny Blanco
    Dec 7, 2016 at 8:46
  • Yes, some people find jQuery easier than SharePoint.. The new Microsoft way is React..
    – Danny '365CSI' Engelman
    Dec 7, 2016 at 12:39
  • Haven't worked with React, but intending to learn and work with it in the future.
    – Benny Blanco
    Dec 7, 2016 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.