Column formatting for date column

[Bhavpreet Bains]

Hello, 

I was trying to format Due Date column to show green with check mark if the Status is completed. However, after adding the column formatting code, it switched the date format from DD/MM/YYYY to YYYY-MM-DDTHH:mm:SSZ.

I am not sure what I am doing wrong. Can anyone please help?

Thank you.

Bhavpreet Bains

Formatting Code:

[#2 · Accepted solution]

Bhavpreet...try using the .toLocalDateString() method in you're formatting as described here.

[#3]

Thanks, Steve! It worked this time. I must be doing something wrong when I tried using the same last time.

[#4]

Hello Steve,

I am wondering if you can help with another query as well.
I am getting Invalid date while trying to do the following. The current Field is of Due Date which is calculated as Created+14. It returns Date Only format for Data and Time data type.

{
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"@currentField",
"1"
]
}
}

Thank you.

[#5]

Bhavpreet...to be clear the @currentField in the snippet below is a calculated field in SharePoint?  If so Calculated fields are not supported only:  

• Single line of text
• Number
• Choice
• Person or Group
• Yes/No
• Hyperlink
• Picture
• Date/Time
• Lookup
• Title (in Lists)

[#6]

Yes, it's a calculated field. If it isn't supported, it all makes sense now.

Thanks again. :)

[#7]

I got around this by setting a date field through Flow when item in the list is created or updated rather than a calculated column

[#8]

Sorry to bug you again.

I thought about it. Since calculated fields aren't supported, I decided to change my approach.
I am now trying to calculate the value using conditional formatting. So, I created a Date/Time column and is using the following code.

However, when I subtract from the date, it works fine. It returns the expected result but addition is still giving me Invalid Date.

{
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"[$Created]",
"1"
]
}
}

[#9]

Hello Alan,

What I am trying to achieve is to change the color of Due Date to amber a few days before the actual deadline. But I am struggling with the addition and subtraction of the days.

I am not sure how Flow will be helpful. If you can share a bit more insight on your approach, may be I will be able to find a better solution. 

Thank you.

[#10]

This is accomplished using date math. You can add milliseconds to any date and the result will be a new date. For example, to add a day to a date, you'd add (24*60*60*1000 = 86,400,000).

{
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"[$Created]",
86400000
]
}
}

[#11]

@Bhavpreet Bains 

Make sure that you have excluded the time format as shown below

Also, you can format your date column in JSON like this 

Date(@@currentField,toDateString())

Please Check the detail steps and most of the JSON Column Formatting tricks at SharePoint Date Column Formatting

[#12]

Annoyingly, I still can't work out what to do  to get this to maintain my local date format. I'd love some help (with an explanation for what is being changed). It's just setting the colour based on the date in the column. I don't seem to be able to just replace 'toDateString()' with 'toLocalDateString()', so there must be something I'm missing. I'd be very grateful for any advice

{
  "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-blockingBackground50",
            {
              "operator": ":",
              "operands": [
                {
                  "operator": "==",
                  "operands": [
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            "@currentField"
                          ]
                        }
                      ]
                    },
                    {
                      "operator": "Date()",
                      "operands": [
                        {
                          "operator": "toDateString()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                },
                "sp-css-backgroundColor-warningBackground50",
                {
                  "operator": ":",
                  "operands": [
                    {
                      "operator": ">",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            {
                              "operator": "toDateString()",
                              "operands": [
                                "@currentField"
                              ]
                            }
                          ]
                        },
                        {
                          "operator": "Date()",
                          "operands": [
                            {
                              "operator": "toDateString()",
                              "operands": [
                                "@now"
                              ]
                            }
                          ]
                        }
                      ]
                    },
                    "sp-css-backgroundColor-successBackground50",
                    ""
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  },
  "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": "@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"
                                  ]
                                }
                              ]
                            }
                          ]
                        },
                        "",
                        ""
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      }
    }
  ]
}