Forum Discussion
SP list - date column and conditional formatting (time zone issue)
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.
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 User
I'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.
Bryan123 as the column is calculated that does change things. I'll try to look at it this evening.
Rob
Los Gallardos
