Forum Discussion

Copper Contributor

Mar 20, 2023

Conditional Formatting of status column based on date

Hi I am almost there with my SharePoint list. I am trying to format holiday status' (Pending/Active/Ended) Based on current date relative to 'Start Date' and 'End Date'. I am using this for...

SharePoint Online

Liron567

Copper Contributor

Mar 24, 2023

Thanks for replying. I can't seem to get this to work as it seems some of the conditions overlap each other. For example if the end date is > now it is shown as active even if the start date hasn't been reached. Any help would be greatly appreciated as this has haunted me for a few weeks now.

TIA
Regards

ganeshsanap

MVP

Mar 26, 2023

Liron567 You have to mention all your conditions properly in your question so that others can understand the problem easily.

From what I understood, you need formula like below:

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

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 28, 2023
ganeshsanap

Thanks for the reply
I think it is easier if I show you the results I get with your formula
The 'ended' condition seems to be working
For the status with the green ring around it the status should be active but it is showing as blank.
The last line with the status showing as 'active' (blue ring) should be shown as 'pending' as the start date has not been met yet.

Any help to iron this out is massively appreciated!

Thanks