=IF([First Date]>0,0.15,IF([Second Date]>0,0.3,IF([Third Date]>0,0.45,IF([Fourth Date]>0,0.6,IF([Fifth Date]>0,0.75,IF([Sixth Date]>0,0.9,IF([Seventh Date]>0,1,0)))))))

This is the formula I am using to determine a rough percentage for a calculated column that is looking at whether 7 dates have been entered - so for example if date 5 has been entered it is 75%, or if date 6 has been entered it is 90%

It only seems to return the value of the first date - 15% even when date 2 or 3 etc have been entered. Any ideas?

  • try reversing conditions from date 7 to date 1 May 12, 2023 at 12:00

1 Answer 1


Try reversing conditions from Seventh Date to First Date like:

=IF([Seventh Date]>0,1,IF([Sixth Date]>0,0.9,IF([Fifth Date]>0,0.75,IF([Fourth Date]>0,0.6,IF([Third Date]>0,0.45,IF([Second Date]>0,0.3,IF([First Date]>0,0.15,0)))))))

You can also use ISBLANK([Date Column Name]) to check if date column is blank or not.


  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.

Official Documentations:

  1. Calculated Field Formulas
  • 1
    Thank you Ganesh! The reversing did the trick :) May 12, 2023 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.