0

I have set up a calculated formula field to calculate the date between a start date and a specified period of months to calculate a renewal date where we have the information.

My problem is that when one of the fields is left empty the calculated field, the renewal date, is coming up with all sorts of crazy dates as it does not have the info it needs. I need to set it up so that if one of the fields is blank is returns no date and comes back blank.

I have tried various formulas with no luck just wondering if anyone can help?

Thanks!

0

please take below link as the reference for calculated fields http://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

and just imagine when one of the two values is null and we are trying to get the dates between the two values? obviously it throw error.. this is what happening in your situation

either make the field as Mandatory or assign a default value to the field it will work fine

0

How about something like this:

=IF(OR(ISBLANK(Column1),ISBLANK(Column2)),"",Column1-Column2)

Check out what functions you can use here, specifically under the "Information Functions" section:

http://www.techonthenet.com/excel/formulas/index.php

(Keep in mind you won't be able to use all of these functions in a calculated column in SharePoint, but I find it a good place to start)

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.