Forum Discussion
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 formula
=IF([Start Date]>NOW(),"Pending",IF([End Date]<TODAY(),"Ended",IF([End Date]>NOW(),"Active","")))
This seems to work for the most part but sometimes as seen below, an end date of todays date will not update the status column.
Any ideas how to change my current formula to make sure these items don't blank?
TIA
- It's possible that the issue is related to the time zone setting in your SharePoint site. The NOW() and TODAY() functions in SharePoint use the time zone setting of the site, which may be different from your local time zone.
You can try adjusting the time zone setting in your SharePoint site to match your local time zone or modify the formula to account for the time zone difference. For example, you can add or subtract the number of hours between your local time zone and the SharePoint site time zone in your formula.
Also, make sure that the 'End Date' column is actually set to the current date and not a date/time value that is slightly earlier or later than the current date. If the date/time value in the 'End Date' column is not exactly equal to the current date, the formula may not work as expected.
Lastly, you can try using the DATEDIF() function instead of the comparison operators (> and <) to calculate the number of days between the current date and the 'Start Date' and 'End Date' columns. This can help to avoid issues with time zone differences and small variations in date/time values. Here's an example formula that uses DATEDIF():
=IF(DATEDIF([Start Date],TODAY(),"d")>0,"Pending",IF(DATEDIF(TODAY(),[End Date],"d")>=0,"Active","Ended"))
This formula first calculates the number of days between the 'Start Date' and today's date using the DATEDIF() function. If the result is greater than 0, it sets the status to "Pending". Otherwise, it calculates the number of days between today's date and the 'End Date' using DATEDIF() and sets the status to "Active" if the result is greater than or equal to 0, or "Ended" otherwise. Liron567 I think you need to reverse the conditions like this:
=IF([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
- 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
RegardsLiron567 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
Try below formula. It should work:
=IF(AND(TODAY()-[Start Date]>=0,TODAY()-[End Date]>=0),"Ended",IF(OR(AND(TODAY()-[Start Date]>=0,TODAY()-[End Date]<0),AND(TODAY()-[Start Date]=-1,TODAY()-[End Date]=-1)),"Active","Pending"))Basically I have used Difference between Start Date / End Date and Today and refined formula according to your requirement. See below screen shot (Consider 21-03-2023 as Today's date)
Hope it will helpful to you and if so then Please mark my response as Best Response & Like to help others in this community
Hi thanks for replying for some reason when my 'end date' is today it is showing as "ended" when I want it to still show as "active". I even recreated your line of start date 21/3 and end date 21/3 and I am getting "ended" instead of what yours is showing as "active". Please see below
I need all the holidays ending on today (21/3) to still be active as they are technically still on leave.
TIA
