When I apply column formatting to a 'date only' date column, the time value is displayed as well

Question

I have a date column, that is set to show date only (not time), named My Date Column.

I have applied the column formatting below which achieves the desired behaviour of:

• If column value is more than 30 days in the future, make text color green and show a CheckMark icon
• If column value is between today and 30 days in the future, make text color red and show an AlarmClock icon
• If column value is today or in the past, make text color red and show an Important icon

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "color": "=if([$My_x0020_Date_x0020_Column]>(@now +2592000000), 'green', if([$My_x0020_Date_x0020_Column]>@now && [$My_x0020_Date_x0020_Column]< (@now +2592000000), 'Red', 'Red'))"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 10px 0 10px"
      },
      "attributes": {
        "iconName": "=if(Number([$My_x0020_Date_x0020_Column]) == 0, '', if([$My_x0020_Date_x0020_Column]>(@now +2592000000), 'CheckMark', if([$My_x0020_Date_x0020_Column]>@now && [$My_x0020_Date_x0020_Column]< (@now +2592000000), 'AlarmClock', 'Important'))"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

However, when I change the column formatting of the last span from this:

{
  "elmType": "span",
  "txtContent": "@currentField"
}

to this:

{
  "elmType": "span",
   "txtContent": "=if(Number(@currentField) == 0, 'N/A', @currentField)"
}

It produces the following:

• Desired Behaviour: If the field is blank, shows the text N/A
• Undesired Behaviour: If the field is not blank, shows a TIME value after the DATE value

To clarify, it shows this:

29/11/2021 23:00:00

when it should show this:

29/11/2021     

Additionally, the date value is different from that which is displayed when the date is not within an if() statement. The act of including @currentField within the if() statement is altering how it is displayed and the date that is displayed.

Question

Why is the time value showing in the date only column and how can I prevent that from happening whilst still retaining the desired conditional column formatting?

Edit

Based on this answer, wrapping @currentField in =toLocaleDateString() seems to work, ie:

{
  "elmType": "span",
  "txtContent": "=if(Number(@currentField) == 0, 'N/A', '=toLocaleDateString(@currentField)')"
}

However, using toLocaleDateString() has the undesired behaviour where if I enter 01/01/2021 as the date in the list item, it is being displayed as 31/12/2020 on the frontend.

I think this is because the method outputs values in user's local time, (from the docs):

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"

Answer 1

It may sound odd, but to display the SharePoint date format as DD/MM/YYYY, please leverage/consider using SharePoint calculated column instead of using JSON column formatting, provided as a built-in option, to answer your question this would be the safest approach.

In the previous answer he was leveraging locale, I don't recommend it because that serves a different purpose and should be done at a site level.

• You can always fallback on your JSON approach, if you don't like this.

1. Open your SharePoint List > All items > Add new column > More.

---

2. Make a new calculated column using below format

 =TEXT(Start,"dd/MM/yyyy")

---

3. Now, it should look something like this when you set it up correctly. Pay close attention that this is built-in SharePoint as an option at the very bottom.

Bonus:

---

You can try to replace the column placeholders with Start there are various other formats available to you once you follow the built in option.

// some more formats to check out
=TEXT(MyDateOnlyCOl,"mmm-dd-yyyy")      
=TEXT(MyDateOnlyCol,"HH-MM")