0

I have 2 columns inside my list:-

  1. [Live/Active Date]. of type Date.
  2. [Contract Length (month)] . of type number, which does not allow decimals.

now based on the above 2 fields, i want to calculate the expiry date, by adding the contract length in month to the live/active date. so i created a calculated column with this formula:-

=DATE(YEAR([Live/Active Date]),(MONTH([Live/Active Date])+[Contract Length (month)]),DAY([Live/Active Date]))

this will work if both fields [Live/Active Date] & [Contract Length (month)] do have values, but if one of them or both of them are null then instead of getting null calculated result, i will get weird results. For example if the [live/Active date] is null while [Contract Length (month)] = 12, i was expecting to get null for the calculated field, while i got this 30/12/3800!! . i am searching for a solution to have my calculated column = null, if any of the 2 fields involved in the formula are null. i do not want to use defualt values or set the fields as required. so is there a way to fix this?

Thanks

3

A null or empty date is typically treated as 12/31/1899.

Try something like this:

=IF( OR(ISBLANK([Live/Active Date] ), ISBLANK([Contract Length (month)]) ),
"missing data", yourEquationHere )

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.