0

I have created a sharepoint workbook with contract information. Three columns in this sharepoint have dates. ([Planned Release Date] , [Revised Release Date] , and [Current Release Date]). I have another column, [On Track], that uses if statements to determine the value for this column, the choices are: "Yes", "No", or "" (blank).

The criteria for the [On Track] column is as follows: If [Planned Release Date] or [Current Release Date] is blank, then [On Track]=""(blank) If [Current Release Date]<=[Planned Release Date] OR [Current Release Date]<=[Revised Release Date] then [On Track]="Yes" If [Curent Release Date]>[Planned Release Date] AND [Current Release Date]>[Revised Release Date] then [On Track]=No

The formula I've written fails when [Current Release Date]>[Planned Release Date] BUT [Current Release Date]<=[Revised Release Date]. (It says the row is Not On Track even though the [Current Release Date]<=[Revised Release Date]).

Here is the formula I have put into sharepoint currently:

=IF(OR([Current Release Date]="",[Planned Release Date]=""),"",IF(AND([Current Release Date]>[Planned Release Date],[Current Release Date]>[Revised Release Date]),"No",IF(OR([Current Release Date]<=[Revised Release Date],[Current Release Date]<=[Planned Release Date]),"Yes")))

Do you see a problem with my formula? Any help / advice is greatly appreciated. Thanks

  • Excel can be useful for sorting out logic issues for calculated columns as it's basically the same syntax. – lgaud Jun 14 '13 at 19:49
0

Having trapped your exceptions in the first two IF statements can you just use an ELSE value for the YES?

=IF(OR([Current Release Date]="",[Planned Release Date]=""),"",IF(AND([Current Release Date]>[Planned Release Date],[Current Release Date]>[Revised Release Date]),"No","Yes"))

I cannot see any problem with the logic of your formula - but this one above works for me in Excel. If you still have issues I would try some simpler formulae to ensure the date values are working out as you expect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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