Jul 12 2018 07:27 AM
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/YYYY to YYYY-MM-DDTHH:mm:SSZ.
I am not sure what I am doing wrong. Can anyone please help?
Thank you.
Bhavpreet Bains
Formatting Code:
Jul 12 2018 07:54 AM
SolutionBhavpreet...try using the .toLocalDateString() method in you're formatting as described here.
Jul 12 2018 08:26 AM
Thanks, Steve! It worked this time. I must be doing something wrong when I tried using the same last time.
Jul 12 2018 12:11 PM
Jul 12 2018 12:18 PM
Bhavpreet...to be clear the @currentField in the snippet below is a calculated field in SharePoint? If so Calculated fields are not supported only:
Jul 12 2018 12:22 PM
Jul 12 2018 12:36 PM
Jul 12 2018 12:53 PM
Jul 12 2018 01:00 PM - edited Jul 12 2018 01:01 PM
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.
Jul 12 2018 02:18 PM
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
]
}
}
Aug 28 2020 06:35 AM
Make sure that you have excluded the time format as shown below
Also, you can format your date column in JSON like this
Date(@@currentField,toDateString())
Please Check the detail steps and most of the JSON Column Formatting tricks at SharePoint Date Column Formatting
Sep 14 2020 07:20 AM
Annoyingly, I still can't work out what to do to get this to maintain my local date format. I'd love some help (with an explanation for what is being changed). It's just setting the colour based on the date in the column. I don't seem to be able to just replace 'toDateString()' with 'toLocalDateString()', so there must be something I'm missing. I'd be very grateful for any advice
{
"elmType": "div",
"style": {
"box-sizing": "border-box",
"padding": "0 2px"
},
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-blockingBackground50",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-warningBackground50",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-successBackground50",
""
]
}
]
}
]
}
]
}
},
"children": [
{
"elmType": "span",
"style": {
"line-height": "16px",
"height": "14px"
},
"attributes": {
"iconName": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
""
]
}
]
}
]
}
]
}
}
},
{
"elmType": "span",
"style": {
"overflow": "hidden",
"text-overflow": "ellipsis",
"padding": "0 3px"
},
"txtContent": "@currentField.displayValue",
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
""
]
}
]
}
]
}
]
}
}
}
]
}
Jul 12 2018 07:54 AM
SolutionBhavpreet...try using the .toLocalDateString() method in you're formatting as described here.