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])"
    }
}

1 Answer 1

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

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.
    – Jerry_MSFT
    Commented Dec 22, 2020 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.