Sharepoint List Calculated Column showing bad results

[GregFIckel]

Hello!

Forgive all the background. I have used forms for our sales team to enter details of items that have been rented as part of the deals they close. Not all deals include all products on the form so I used branching. If the client orders Item #1 then the sales person selects Yes and are presented with Start_Date, End_date and  the number of months they need to renew the contract before it expires. If they select No then they skip to Item #2 and so on... Power Automate then passes the information into the SharePoint list. 

So here is the issue if they client does not order one of the items listed the data for that field is not present in the list in SharePoint. I have created a calculated field in list that looks at the end_date filed and subtracts the number of months stored in the Renewal filed from the date and displays the date the Renewal should begin. but on the lines where a given product was not ordered (say item #3) the math comes up with a crazy date. see image below:

Here is the formula for the column: 

=DATE(YEAR([itm1 End]),MONTH([itm End])-([itm Renewal Start]),DAY([CRM End]))

I am guessing I need an IF to keep calculations from returning a fate when no end date was provided. I just cant figure out how to write it without causing an error. Any thoughts you may have would be great! 

[#2 · Accepted solution]

@GregFIckel  

This formula works, you'll have to update the to use the correct field names. IF statements are pretty easy once you do them a few times.

=IF (condition, condition is true, condition is false)

So if a date field is empty, use your formula between them commas, other leave it blank. 

=IF([End]>0,DATE(YEAR([End]),MONTH([End])-([Renewal]),DAY([Start])),"") 

[#3]

Thank you @PamDeGraffenreid this is great! and worked like a charm!