SOLVED

Column formatting for date column

Highlighted
Frequent Contributor

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.

date.PNG

 

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

 

Thank you.

Bhavpreet Bains

Formatting Code:

Formatting Code.PNG

 

11 Replies
Highlighted
Best Response confirmed by Bhavpreet Bains (Frequent Contributor)
Solution

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

Highlighted

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

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

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)
Highlighted
Yes, it's a calculated field. If it isn't supported, it all makes sense now.

Thanks again. :)
Highlighted
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
Highlighted
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"
]
}
}
Highlighted

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.

Highlighted

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
]
}
}

Highlighted

@Bhavpreet Bains 

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

Only-show-date-without-time-in-JSON-column-formatting

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

Highlighted

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"
                                  ]
                                }
                              ]
                            }
                          ]
                        },
                        "",
                        ""
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      }
    }
  ]
}