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 colorgreen
and show aCheckMark
icon - If column value is
between today and 30 days in the future
, make text colorred
and show anAlarmClock
icon - If column value is
today or in the past
, make text colorred
and show anImportant
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 theDATE
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"