0

I am trying to format a column that reacts to a value from another column, one of the columns "Projected End Date" is a date format. This has the column formatting applied and when an adjacent column "Project Overrunning" changes to "Overrun" I am changing the value within the "Projected End Date" column to "Overrunning".

This part I've got working but the issue is that the date in the "Projected End Date" is formatted DD/MM/YYYY, HH:MM:SS and I'd like to remove the HH:MM:SS. In the past I usually do this by just changing txtContent to "@currentField" and this strips the minutes away, but it appears when I use this in a IF statement it doesn't work.

I've tried to use toLocaleDateString() but I can't seem to get the syntax correct.

Minutes showing:

enter image description here

Value changed to "Overrunning":

enter image description here

This is how far I've got:

{
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "==",
          "operands": [
            "[$Project_x0020_Overruning]",
            "Overrun"
          ]
        },
        "sp-css-backgroundColor-errorBackground50",
        ""
      ]
    }
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "line-height": "16px",
        "height": "14px"
      },
      "attributes": {
        "iconName": {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "[$Project_x0020_Overruning]",
                "Overrun"
              ]
            },
            "",
            ""
          ]
        }
      }
    },
    {
      "elmType": "span",
      "style": {
        "overflow": "hidden",
        "text-overflow": "ellipsis",
        "padding": "0 3px"
      },
      "txtContent": "=if([$Project_x0020_Overruning] == 'Overrun', 'Overrunning', '@currentField')",
      "attributes": {
        "class": {
          "operator": ":",
          "operands": [
            {
              "operator": "==",
              "operands": [
                "[$Project_x0020_Overruning]",
                "Overrun"
              ]
            },
            "",
            ""
          ]
        }
      }
    }
  ]
}

If someone can point me in the right direction that would be great!

1
  • Try my answer given below and let me know if it works for you. – Ganesh Sanap Nov 12 '20 at 16:07
1

Try using this:

"txtContent": "=if([$Project_x0020_Overruning] == 'Overrun', 'Overrunning', toLocaleDateString(@currentField))"

toLocaleDateString(): returns a language sensitive representation of just the date portion of a date.

  • "txtContent":"=toLocaleDateString(@now)" results vary based on user's locale, but en-us looks like "2/5/2019".

Reference: Use column formatting to customize SharePoint - Operators

2
  • Also, I see you have added on span children with blank iconName and class attribute in last children element. If that is intentional then it is fine. But, if you want to remove it and want to optimize the code then let me know. I will update the answer with optimized code by removing the empty attribute code. – Ganesh Sanap Nov 12 '20 at 16:07
  • Hi Ganesh, that worked perfectly, strange as I am sure I tried that...but obviously didn't :) I am using the code that SharePoint produces as my template so any advice on making this better would be great. – RobC_CTL Nov 14 '20 at 11:16
0

You could just try to change to column setting. enter image description here

enter image description here

1
  • 1
    The column formatting overrides this setting – RobC_CTL Nov 14 '20 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.