In a SharePoint list I want to make the End Date Required when the Task Status = Complete

Question

In a SharePoint list I want to make the End Date Required when the Task Status = Complete

Marcia Bester 20

Hi,

I am busy setting up KPI's via a SP list.

In this I have fields, Task Status and End Date, I want the End Date to become required when the Task Status is changed to Completed. I also want to ensure that the End Date may not be the same as the Start Date or earlier than the Start Date

I have looked for similar questions, however most are about choice field. I am not able to use Power Apps or Tools

Answer 1

Emily Du-MSFT 36,776 Microsoft Vendor

Please go to List settings -> Validation Settings -> Use following formula.

=IF([Start Date]<[End Date], IF([Task Status]="Completed", IF([End Date]="",FALSE,TRUE),TRUE))

User's image

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Marcia Bester 20 Reputation points

2023-11-08T10:41:57.58+00:00

Hi thanks for this. I have to add this to the End Date as Validation, was my assumption.

This is how the list looks.

I copied the solution and entered it into the field and I got this response. I am using O365 Sharepoint.
Emily Du-MSFT 36,776 Reputation points Microsoft Vendor

2023-11-09T03:11:05.9966667+00:00

What is the type of the Task Status column?

Could you provide the screenshot of validation settings?
Marcia Bester 20 Reputation points

2023-11-09T07:39:04.87+00:00

Hi Emily sure. There is nothing in there now as I had to save the field as I am in Europe. Though I copied and pasted your formula as you described in your answer.
Emily Du-MSFT 36,776 Reputation points Microsoft Vendor

2023-11-09T08:20:43.4566667+00:00

1.Sometimes comma (,) does not work in formula (I am not sure, but it is based on language or regional settings in the site). So, in that case use semicolon (;) instead of comma (,).

2.Make sure that you use correct display name of columns (case-sensitivity, etc.).
Marcia Bester 20 Reputation points

2023-11-09T08:27:50.8233333+00:00

Hi thanks for the quick response - the default language is USA - English. I inserted the following and got the below Error Message

=IF([Start Date]<[End Date]; IF([Task Status]="Completed"; IF([End Date]="";FALSE;TRUE);TRUE))
Emily Du-MSFT 36,776 Reputation points Microsoft Vendor

2023-11-09T08:33:55.3133333+00:00

The formula cannot be used in the column validation.

It must need to be used in the list validation.

Go to List settings -> Validation Settings -> Use following formula.

=IF([Start Date]<[End Date],IF([Task Status]="Completed",IF([End Date]="",FALSE,TRUE),TRUE))

or

=IF([Start Date]<[End Date];IF([Task Status]="Completed";IF([End Date]="";FALSE;TRUE);TRUE))
Marcia Bester 20 Reputation points

2023-11-09T10:25:46.46+00:00

Thank you so much for your help, I am sort of learning as I go along here. Am I able to put multiple validations in the list validation?
Marcia Bester 20 Reputation points

2023-11-09T15:43:27.1266667+00:00

Hi Though the formula is working now - it won't allow me to capture new data. I get this error message.
Emily Du-MSFT 36,776 Reputation points Microsoft Vendor

2023-11-10T02:24:57.1+00:00

Please try the new formula.

=IF(OR([Start Date]<[End Date],[End Date]=""),IF([Task Status]="Completed",IF([End Date]="",FALSE,TRUE),TRUE),FALSE)

OR

=IF(OR([Start Date]<[End Date];[End Date]="");IF([Task Status]="Completed";IF([End Date]="";FALSE;TRUE);TRUE);FALSE)
Marcia Bester 20 Reputation points

2023-11-10T10:09:14.7633333+00:00

Hi, this works awesomely, thank you for your support and assistance :D

In a SharePoint list I want to make the End Date Required when the Task Status = Complete

0 additional answers

Activity