Forum Discussion

Clause9's avatar
Clause9
Copper Contributor
Jun 13, 2023

Yet Another Conditional Formatting Query

Hey folks, I have an SP list with 3 relevant columns - IssueDate, ReviewFrequency, and NextReview.

 

IssueDate is for manual date entry, ReviewFrequency is a choice of N/A, 3, 6, 12, 24, 36. NextReview is a calculated column that adds number value in ReviewFrequency to IssueDate.

 

I have applied JSON formatting in Format View to format rows where NextReview is less than today (@now) in sp-field-severity--severeWarning, rows where next review is due within 30 days (2592000000ms) in sp-field-severity--Warning, and all other rows in alternating sp-css-backgroundColor-BgLightGray30 and sp-css-backgroundColor-noFill (giving zebra alternate banding).

 

All works well apart from an issue with dates being read by conditional formatting as MM-dd-yyyy not dd-MM-yyyy as illustrated here: 

 

How do i ensure dates in the columns are seen / interpreted as dd-MM-yyyy in the conditional formatting rules? 

 

Thanks in advance,

 

dates
format
Lists
SharePoint Online
  • SvenSieverding's avatar
    SvenSieverding
    Bronze Contributor
    Jun 13, 2023

    Hi Clause9 ,

    You can use the toLocaleDateString function to format a column in the date format of your locale.

    =toLocaleDateString(@currentField)

    Make sure that your calculated column "NextReview" returns the data type "date".

    Alternatively you can use the "getMonth", "getDate" and "getYear" functions to build your own format

    =getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now)



    If you want to compare two dates you can use the "Number" function if you don't want to compare dates directly. It returns the number of milliseconds since ???

    Number(@currentField)>Number(@now)


    Best Regards,
    Sven

    • Clause9's avatar
      Clause9
      Copper Contributor
      Jun 13, 2023
      Hi Sven and thanks for the very swift response. All sorted but quite strange 🙂

      Good old ChatGPT suggested using toLocaleDateString in the JSON, I tried it and it didnt work - it actually removed the conditional formatting that had worked.

      Removed and saved back to JSON that uses toDateString. I still had the error so thought I'd add another calculated column to validate what the other columns were returning - when i added the column, all formatting started working "properly". Have now removed column and its still working..

      Modern technology never fails to confuse me 🙂
      • Clause9's avatar
        Clause9
        Copper Contributor
        Jun 13, 2023

        Wow, even more confused now 🙂

         

        I closed my browser, cleared history and cache then reopened the list - formatting was perfect - rows with Next Review < today were in severeWarning, rows due in next 30 days were in Warning, all other rows were alternating no format / light grey. Happy days 🙂

         

        I then did some work in another list before reopening the first - Aaaarrrggghhhh, the formatting is "broken" again. Here's code I'm using in Format View - grateful if anyone could debug and let me know what I've done wrong....

         

         

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": {
    "operator": "?",
    "operands": [
      {
        "operator": "<=",
        "operands": [
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "[$Next_x0020_Review]"
                    ]
                  }
                ]
              }
            ]
          },
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "@now"
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "sp-field-severity--severeWarning",
      {
        "operator": "?",
        "operands": [
          {
            "operator": "<=",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "[$Next_x0020_Review]"
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "+",
                        "operands": [
                          "@now",
                          2592000000
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          },
          "sp-field-severity--warning",
          {
            "operator": "?",
            "operands": [
              {
                "operator": "==",
                "operands": [
                  {
                    "operator": "%",
                    "operands": [
                      "@rowIndex",
                      2
                    ]
                  },
                  0
                ]
              },
              "sp-css-backgroundColor-BgLightGray30",
              "sp-css-backgroundColor-noFill"
            ]
          }
        ]
      }
    ]
  }
}

         

        Clause9 

Resources