Forum Discussion
SP list - date column and conditional formatting (time zone issue)
I'm trying to do some simple conditional formatting to a date column but when I use the built in features, it changes the format of the date so it's not as user friendly
7/29/2020 turns into 2020-07-29T05:00:00Z
Format is
If the date is before today, turn it red
If the date is today's date, turn yellow
If the date is after today's date, turn green
Can someone assist me with keeping the date friendly to read? Column name is "Roll Off Date"
Bryan123 To convert the date & time field in date only format, you can use toLocaleDateString() function. You will get more information for this function in official documentation at: Use column formatting to customize SharePoint
Below is the working code I tried. Major hint is to use: "txtContent": "=toLocaleDateString(@currentField.displayValue)" in your JSON code (Advanced mode). Rest all the JSON code is same.
{ "elmType": "div", "style": { "box-sizing": "border-box", "padding": "0 2px" }, "attributes": { "class": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-warningBackground", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-successBackground", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-errorBackground", "" ] } ] } ] } ] } }, "children": [ { "elmType": "span", "style": { "line-height": "16px", "height": "14px" }, "attributes": { "iconName": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", "" ] } ] } ] } ] } } }, { "elmType": "span", "style": { "overflow": "hidden", "text-overflow": "ellipsis", "padding": "0 3px" }, "txtContent": "=toLocaleDateString(@currentField.displayValue)", "attributes": { "class": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", "" ] } ] } ] } ] } } } ] }Note: Make sure the data type of your calculated column is Date & Time.
Please click Mark as Best Response if my post helped you solve your issue. This will help others find the correct solution easily. It also closes the item. If the content was useful in other ways, please consider giving it Like.
4 Replies
Bryan123 are you using advanced JSON column formatting or just the column formatting that is out-of-the box? The out-of-the-box formatting works fine (for me anyway) on date columns:
Rob
Los Gallardos
Microsoft Power Automate Community Super UserI'm using the out the box feature. I forgot to mention but the column in question is based on a formula so that's what is probably adding the tz.
The formula basically takes the date from a previous column and adds 30 to it
Here is how it looks before and with the formatting
Bryan123 To convert the date & time field in date only format, you can use toLocaleDateString() function. You will get more information for this function in official documentation at: Use column formatting to customize SharePoint
Below is the working code I tried. Major hint is to use: "txtContent": "=toLocaleDateString(@currentField.displayValue)" in your JSON code (Advanced mode). Rest all the JSON code is same.
{ "elmType": "div", "style": { "box-sizing": "border-box", "padding": "0 2px" }, "attributes": { "class": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-warningBackground", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-successBackground", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "sp-css-backgroundColor-errorBackground", "" ] } ] } ] } ] } }, "children": [ { "elmType": "span", "style": { "line-height": "16px", "height": "14px" }, "attributes": { "iconName": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", "" ] } ] } ] } ] } } }, { "elmType": "span", "style": { "overflow": "hidden", "text-overflow": "ellipsis", "padding": "0 3px" }, "txtContent": "=toLocaleDateString(@currentField.displayValue)", "attributes": { "class": { "operator": ":", "operands": [ { "operator": "==", "operands": [ "@currentField", "" ] }, "", { "operator": ":", "operands": [ { "operator": "<", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": ">", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@currentField" ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ "@now" ] } ] } ] }, "", "" ] } ] } ] } ] } } } ] }Note: Make sure the data type of your calculated column is Date & Time.
Please click Mark as Best Response if my post helped you solve your issue. This will help others find the correct solution easily. It also closes the item. If the content was useful in other ways, please consider giving it Like.
