SP list - date column and conditional formatting (time zone issue)

[Bryan123]

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"

[#2]

@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

[#3]

@RobElliott 

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

[#4]

@Bryan123 as the column is calculated that does change things. I'll try to look at it this evening.

Rob
Los Gallardos

[#5 · Accepted solution]

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