Forum Discussion

blueflame2130's avatar
blueflame2130
Copper Contributor
Apr 25, 2022

sharepoint formatting

Hi I have a sharepoint file for boiler servicing , I've been trying for a month to try and work out how to format the following . 

Boiler Due date 2022 , Boiler Inspection date 2022 , kpi pass/fail

I'm trying to work out if i can get the kpi to turn red and say fail if the inspection date is after the due date and if I can get the kpi to turn green and say pass if the inspection date is on or before the due date.

mant thanks for any help or advice

SharePoint Online
sharepoint server

4 Replies

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Apr 25, 2022

    blueflame2130 add a calculated column called KPI with the following formula that compares the BoilerDue and BoilerInspection columns:

    =If([BoilerInspection] > [BoilerDue], "Fail","Pass")

     

    Then format the KPI column with the following JSON (advanced mode):

     

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "padding-left": "14px",
    "background-color": "=if(@currentField =='Fail', '#c00000','teal')",
    "color": "white"
  }
}

     

     

     

    The result is as follows:

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

     

    • blueflame2130's avatar
      blueflame2130
      Copper Contributor
      Apr 26, 2022

      RobElliott many thanks for that I really appreciate it . Can I ask would it still work if I put dates in the description or not ?  just I cant seem to get it to work if I use BoilerDue2022 and BoilerInspection2022 and KPI2022 

       

      Many Thanks 

      • RobElliott's avatar
        RobElliott
        Silver Contributor
        Apr 26, 2022

        blueflame2130 yes it will still work, they are just text column names. But always check the internal name of a column, particularly if you change it! Go to list settings, edit the column and in the url at the top the internal name is after field=

         

        Rob
        Los Gallardos
        Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Resources