sharepoint formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-3294103%22%20slang%3D%22en-US%22%3Esharepoint%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294103%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20a%20sharepoint%20file%20for%20boiler%20servicing%20%2C%20I've%20been%20trying%20for%20a%20month%20to%20try%20and%20work%20out%20how%20to%20format%20the%20following%20.%26nbsp%3B%3C%2FP%3E%3CP%3EBoiler%20Due%20date%202022%20%2C%20Boiler%20Inspection%20date%202022%20%2C%20kpi%20pass%2Ffail%3C%2FP%3E%3CP%3EI'm%20trying%20to%20work%20out%20if%20i%20can%20get%20the%20kpi%20to%20turn%20red%20and%20say%20fail%20if%20the%20inspection%20date%20is%20after%20the%20due%20date%20and%20if%20I%20can%20get%20the%20kpi%20to%20turn%20green%20and%20say%20pass%20if%20the%20inspection%20date%20is%20on%20or%20before%20the%20due%20date.%3C%2FP%3E%3CP%3Emant%20thanks%20for%20any%20help%20or%20advice%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3294103%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3296070%22%20slang%3D%22en-US%22%3ERe%3A%20sharepoint%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3296070%22%20slang%3D%22en-US%22%3EMany%20thanks%20Rob%20I've%20managed%20to%20get%20that%20working%20now%20you%20have%20saved%20my%20head%20from%20exploding%20lol%20%2C%20just%20need%20to%20try%20and%20get%20the%20advanced%20bit%20for%20the%20colour%20to%20work%20now%20for%20some%20reason%20it%20wont%20let%20me%20save%20the%20code%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3295062%22%20slang%3D%22en-US%22%3ERe%3A%20sharepoint%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1372733%22%20target%3D%22_blank%22%3E%40blueflame2130%3C%2FA%3E%26nbsp%3Byes%20it%20will%20still%20work%2C%20they%20are%20just%20text%20column%20names.%20But%20always%20check%20the%20internal%20name%20of%20a%20column%2C%20particularly%20if%20you%20change%20it!%20Go%20to%20list%20settings%2C%20edit%20the%20column%20and%20in%20the%20url%20at%20the%20top%20the%20internal%20name%20is%20after%20field%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3295059%22%20slang%3D%22en-US%22%3ERe%3A%20sharepoint%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%26nbsp%3Bmany%20thanks%20for%20that%20I%20really%20appreciate%20it%20.%20Can%20I%20ask%20would%20it%20still%20work%20if%20I%20put%20dates%20in%20the%20description%20or%20not%20%3F%26nbsp%3B%20just%20I%20cant%20seem%20to%20get%20it%20to%20work%20if%20I%20use%20BoilerDue2022%20and%20BoilerInspection2022%20and%20KPI2022%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20Thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294151%22%20slang%3D%22en-US%22%3ERe%3A%20sharepoint%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1372733%22%20target%3D%22_blank%22%3E%40blueflame2130%3C%2FA%3E%26nbsp%3Badd%20a%20calculated%20column%20called%20KPI%20with%20the%20following%20formula%20that%20compares%20the%20BoilerDue%20and%20BoilerInspection%20columns%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIf(%5BBoilerInspection%5D%20%26gt%3B%20%5BBoilerDue%5D%2C%20%22Fail%22%2C%22Pass%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20format%20the%20KPI%20column%20with%20the%20following%20JSON%20(advanced%20mode)%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%20%20%22%24schema%22%3A%20%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fcolumn-formatting.schema.json%22%2C%0A%20%20%22elmType%22%3A%20%22div%22%2C%0A%20%20%22txtContent%22%3A%20%22%40currentField%22%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22padding-left%22%3A%20%2214px%22%2C%0A%20%20%20%20%22background-color%22%3A%20%22%3Dif(%40currentField%20%3D%3D'Fail'%2C%20'%23c00000'%2C'teal')%22%2C%0A%20%20%20%20%22color%22%3A%20%22white%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20is%20as%20follows%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22kpi.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366611iCC6D51AB1D0D84AC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22kpi.png%22%20alt%3D%22kpi.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies

@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:

kpi.png

 

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

 

@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 

@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)

Many thanks Rob I've managed to get that working now you have saved my head from exploding lol , just need to try and get the advanced bit for the colour to work now for some reason it wont let me save the code