Forum Discussion
Solved
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...
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.
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.