Forum Discussion
Solved
If/Then ISBLANK in SharePoint Online Calculated Column
I'm sure this is a common need, but I'm drawing a blank. I am building a Calculated Column for which I need an if/then statement to check the "PermitActual" field and if it is blank, insert today's ...
Arnie_Howes try it with =IF(PermitActual=0,TODAY(),PermitActual)
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
Arnie_Howes try it with =IF(PermitActual=0,TODAY(),PermitActual)
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
RobElliott Hello. I have a follow-on question to this. I have a calculated column I've called "FailPass" that needs to look across a series of choice columns to calculate a score, but also account for blanks.
The below formula saves as a pass/fail score even if the user left some of the questions unanswered (question choice column is blank). I would rather the FailPass column show as blank until all the necessary questions are answered, which would allow a user to return to a saved item and finish completing it if they were interrupted during the task, or filter on items in the list that are without a FailPass score.
How should I adjust this?
=IF(OR([Question 1?]="Inaccurate",[Question 2?]="Inaccurate",[Question 3?]="Inaccurate",[Question 4?]="Inaccurate",[Question 5?]="Inaccurate",[Question 6?]="Inaccurate",[Question 7?]="Inaccurate"),"Fail","Pass").
To further complicate this scenario, only certain questions need to be answered based on the type of review a user is completing. In total, this list has about 20 questions that vary based on the selection a user makes in a different choice column I've called "ReviewType". So I am concerned that a calculated field may not recognize when these question columns should indeed be blank based on review type. Should I create a calculated FailPass column for each review type?
Thanks in advance for your insight.CristinaMorales Try using formula like:
=IF(OR(ISBLANK([Question 1?]),ISBLANK([Question 2?]),ISBLANK([Question 3?]),ISBLANK([Question 4?]),ISBLANK([Question 5?]),ISBLANK([Question 6?]),ISBLANK([Question 7?])),"",IF(OR([Question 1?]="Inaccurate",[Question 2?]="Inaccurate",[Question 3?]="Inaccurate",[Question 4?]="Inaccurate",[Question 5?]="Inaccurate",[Question 6?]="Inaccurate",[Question 7?]="Inaccurate"),"Fail","Pass")Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs
- Hello, thank you for this. I have not been able to get this to work yet. I tested by saving an item where some choice columns were left blank on the form, and the calculated column still filled in with a "pass" when I need the field stay blank until all questions are answered. I want the calculated column look at each question and return a blank any of the question choice columns are blank, or return a "fail" if three specific questions are marked as "Inaccurate".
This is what it looks like; what am I missing?
=IF(OR(ISBLANK([Question1?]),ISBLANK([Question2?]),ISBLANK([Question3?]),ISBLANK([Question4?]),ISBLANK([Question5?]),ISBLANK([Question6?]),ISBLANK([Question7?]),ISBLANK([Question8?])),"",IF(OR([Question1?]="Inaccurate",[Question2?]="Inaccurate",[Question3?]="Inaccurate"),"Fail","Pass")
- RobElliott, it worked like a charm. Thanks!
