#StackBounty: #sharepoint-online #conditional-formatting #sharepoint-list When I apply column formatting to a 'date only' date …

Bounty: 50

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

Good

The column formatting below 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"
    }
  ]
}

Bad

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 achieves the desired behaviour of showing the text N/A if the field is blank.

However, it also produces the undesired behaviour of displaying a TIME value after the DATE value for all values that are not blank, ie it shows this:

29/11/2021 23:00:00

when it should be showing this:

29/11/2021     

The act of including @currentField within the if conditional (as the if_false argument) seems to be altering how it is normally displayed (ie without the time value).

Question

Why is the time value now showing in the date only date column and how can I prevent that from happening?

Edit

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

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

I’m not sure if it will have any undesired consequences though.

For example, I think this 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"

Concerns

What is the default behaviour of a date column?

Does it output value’s in user’s local time, or does it retain the value that is explicitly entered into the field?

Therefore does using toLocaleDateString() alter what the end-user will see (as compared to default date column ouput)?

Update: I can confirm undesired behaviour of toLocaleDateString(), ie that if I enter01/01/2021 as the date in the list item, it is being displayed as 31/12/2020 on the frontend.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.