Help with Conditional Statement (Calculated Column)

Question

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

Answer 1

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.