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 for...
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.
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.