0

I have a custom list that displays the expiry date of Certificate 1 and Certificate 2. Candidates must have both to be considered Certified. The Certification expiry date is the earlier of the two certificates so I use the following formula

=MIN([Certificate 1 expiry date],[Certificate 2 expiry date])

This works fine until somebody doesn't have an expiry date for one of the certificates and then the Certification Expiry Date is 30/12/1899.

How do I get the Certification Expiry Date to be blank if they are missing one of the certificates?

I asked a similer question on Stack Overflow and I was given the following formula that works in Excel but not in SharePoint? Can MIN and ISBLANK be used together in a calculated column?

=IF(OR(ISBLANK(Certificate 1 expiry date),ISBLANK(Certificate 2 expiry date)),"",MIN(Certificate 1 expiry date,Certificate 2 expiry date))
  • SharePoint 2007, 2010 or 2013 ? – JayHell Jul 24 '14 at 14:52
  • SharePoint 2007 – Enilorac Jul 24 '14 at 15:00
1

I used this formula in WSS 3.0 (12.0.0.6690) and it worked.

=IF(OR(ISBLANK(date1),ISBLANK(date2)),"",MIN(date1,date2))

enter image description here

Check for your separtors if you use a different regional setting than English.

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.