0

I have JSON formatted a column with the following:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "a", "txtContent": "*****", "attributes": { "target": "_blank", "href": "='https://*****.sharepoint.com/sites/group-*****/Lists/*****%20*****/AllItems.aspx?FilterField1=Person&FilterValue1=' + [$Author.title] + '&FilterField2=My_x0020_DateColumn&FilterValue2=' + toLocaleDateString([$Start])" } }

It outputs a link to another list that is filtered on a Person and Date column.

The link creation works correctly, however the date from toLocaleDateString() is output as:

21/12/2020

whereas it needs to be:

2020-12-21

or perhaps even:

2020-12-21&FilterDisplay2=21%2F12%2F2020

for the filter to be applied correctly (even though the value in the column is actually displayed as 21/12/20).

How can I output the date value as YYYY-MM-DD so that the filter is applied correctly?

Edit

Have implemented @Jerry_MSFT 's solution as follows - for some reason it is returning month as 11 rather than 12 (does getMonth() return a 0 indexed value? or is something weird happening?):

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "a", "txtContent": "*****", "attributes": { "target": "_blank", "href": "='https://*****.sharepoint.com/sites/group-*****/Lists/*****%20*****/AllItems.aspx?FilterField1=Person&FilterValue1=' + [$Author.title] + '&FilterField2=My_x0020_DateColumn&FilterValue2=' + getYear([$Start])+'-'+getMonth([$Start])+'-'+getDate([$Start])" } }

Edit 2

Month does seem to be 0 indexed, this seems to work:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "a", "txtContent": "*****", "attributes": { "target": "_blank", "href": "='https://*****.sharepoint.com/sites/group-*****/Lists/*****%20*****/AllItems.aspx?FilterField1=Person&FilterValue1=' + [$Author.title] + '&FilterField2=My_x0020_DateColumn&FilterValue2=' + getYear([$Start]) + '-' + (getMonth([$Start])+1) + '-' + getDate([$Start])" } }
Improve this question

1 Answer 1

Reset to default
3

Per my test and search, it seems the toLocaleDateString() cannot convert date format. We can do it manually with an expression like this:

=getYear(@currentField)+'-'+(getMonth(@currentField)+1)+'-'+getDate(@currentField)

Just as OP mentioned, the getMonth returns a 0-based value, we need to add 1 for it.

getMonth: returns the month in the specified date according to local time, as a zero-based value (where zero indicates the first month of the year). - Only available in SharePoint Online

enter image description here

enter image description here

Improve this answer
3
  • Have added my attempt of implementation to original post. Month is being returned as 11 rather than 12 - does getMonth() return 0 indexed value? (edit: looks like it does, so need to know how to add, not concatenate, 1 to the value returned by getMonth()) Commented Dec 21, 2020 at 22:58
  • Update: Have added solution to OP. Commented Dec 21, 2020 at 23:09
  • @user1063287, yes it is a zero-based value so we need to add 1 for it. I updated the answer, please check. =getYear(@currentField)+'-'+(getMonth(@currentField)+1)+'-'+getDate(@currentField) works in my end. Commented Dec 22, 2020 at 1:26

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.