Forum Discussion

Bhavpreet Bains's avatar
Bhavpreet Bains
Iron Contributor
Jul 12, 2018
Solved

Column formatting for date column

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/Y...
SharePoint Online
  • spucelik's avatar
    spucelik
    Jul 12, 2018

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

Bhavpreet Bains's avatar
Bhavpreet Bains
Iron Contributor
Jul 12, 2018
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.
spucelik's avatar
spucelik
Icon for Microsoft rankMicrosoft
Jul 12, 2018

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)
  • Bhavpreet Bains's avatar
    Bhavpreet Bains
    Iron Contributor
    Jul 12, 2018
    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"
    ]
    }
    }
    • spucelik's avatar
      spucelik
      Icon for Microsoft rankMicrosoft
      Jul 12, 2018

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

  • Bhavpreet Bains's avatar
    Bhavpreet Bains
    Iron Contributor
    Jul 12, 2018
    Yes, it's a calculated field. If it isn't supported, it all makes sense now.

    Thanks again. :)
    • Alan Marshall's avatar
      Alan Marshall
      Iron Contributor
      Jul 12, 2018
      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
      • Bhavpreet Bains's avatar
        Bhavpreet Bains
        Iron Contributor
        Jul 12, 2018

        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.

Resources