Forum Discussion

Copper Contributor

Mar 10, 2023

Update SharePoint column status depending on if a date has passed or not

Hi I am trying to do an auto update column for status of staff holiday (pending, active, ended) I have 2 date columns 'start date' and 'end date' I have set up conditional formatting on ...

MVP

Mar 11, 2023

Liron567 Try using this:

=IF([Start Date]>TODAY(),"Pending",IF([End Date]>TODAY(),"Active",IF([End Date]<TODAY(),"Ended","")))

If not working, you can try using NOW() instead of TODAY()

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

Copper Contributor

Mar 13, 2023

Thank you, this looks similar to mine. Could you please tell me what you changed.

TIA

ganeshsanap
MVP
Mar 14, 2023
Liron567 IF function in SharePoint calculated formula has syntax like below:

IF(logical_test, value_if_true, value_if_false)

You did not provide value_if_false parameter in your formula for last IF function.
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
- Liron567
  Copper Contributor
  Mar 14, 2023
  see below
  - Liron567
    Copper Contributor
    Mar 14, 2023
    I changed the formula to
    
    =IF([Start Date]>NOW(),"Pending",IF([End Date]>NOW(),"Active",IF([End Date]<TODAY(),"Ended","")))
    
    Now I have an issue with the "ended" status not working correctly.
    I need it to be so that if the holiday ends on 14th for example it will only show ended from the 15th onwards (because 14th should still be an active holiday)

Resources