SOLVED
Home

JSON Formatting Referencing Calculated Date Column on SharePoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-292913%22%20slang%3D%22en-US%22%3EJSON%20Formatting%20Referencing%20Calculated%20Date%20Column%20on%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292913%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20hoping%20someone%20can%20help%20me%20so%20I'm%20not%20banging%20my%20head%20on%20the%20wall%20trying%20to%20find%20what%20I%20think%20should%20be%20a%20very%20simple%20answer.%26nbsp%3B%20Basic%20scenario%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20color%20the%20text%20in%20a%20choice%20column%20based%20on%20the%20date%20shown%20in%20a%20calculated%20date%20column.%20This%20works%20perfectly%20fine%20when%20referencing%20a%20date%20column%2C%20but%20not%20when%20referencing%20a%20calculated%20date%20column.%20It%20seems%20the%20issue%20is%20JSON%20doesn't%20read%20the%20column%20as%20a%20date%20value%20and%20searching%20extensively%20on%20how%20to%20have%20it%20read%20it%20as%20a%20date%20value%20I%20can't%20find%20anything.%20Here%20is%20an%20example%20of%20the%20JSON%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3CBR%20%2F%3E%22%24schema%22%3A%20%22%3CA%20href%3D%22http%3A%2F%2Fcolumnformatting.sharepointpnp.com%2FcolumnFormattingSchema.json%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fcolumnformatting.sharepointpnp.com%2FcolumnFormattingSchema.json%3C%2FA%3E%22%2C%3CBR%20%2F%3E%22elmType%22%3A%20%22div%22%2C%3CBR%20%2F%3E%22txtContent%22%3A%20%22%40currentField%22%2C%3CBR%20%2F%3E%22style%22%3A%20%7B%3CBR%20%2F%3E%22color%22%3A%20%7B%3CBR%20%2F%3E%22operator%22%3A%20%22%3F%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22operator%22%3A%20%22%26lt%3B%3D%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%22%3CEM%3E%5B%24CalculatedDateColumn%5D%3C%2FEM%3E%22%2C%3CBR%20%2F%3E%22%40now%22%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%2C%3CBR%20%2F%3E%22%23a80000%22%2C%3CBR%20%2F%3E%22%22%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20if%20the%20italicized%20is%20a%20date%20column%20and%20I'm%20guessing%20there's%20a%20way%20to%20have%20JSON%20treat%20it%20as%20a%20date%20column%2C%20right%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-292913%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EJson%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293445%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Formatting%20Referencing%20Calculated%20Date%20Column%20on%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293445%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20anyone%20else%20banging%20their%20head%20against%20the%20wall%20trying%20to%20get%20a%20similar%20scenario%20to%20work%20I%20found%20a%20solution%20after%20coming%20across%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FSharePoint%2Fsp-dev-list-formatting%2Fissues%2F92%23issuecomment-431657133%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20comment%20in%20a%20discussion%20on%20GitHub%3C%2FA%3E.%20Changing%20my%20calculated%20column%20to%20text%20instead%20of%20date%20and%20using%20the%20formula%20to%20display%20as%20a%20date%20allows%20the%20JSON%20formatting%20to%20%22see%22%20the%20date%20value%20to%20format%20the%20Phase%20column%20if%20the%20date%20is%20in%20the%20past.%20My%20calculated%20column%20is%20looking%20at%20multiple%20date%20(project%20phase%20gates)%20columns%20to%20give%20the%20next%20milestone%2C%20but%20in%20simple%20form%20it%20works%20in%20the%20attached%20screenshot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293416%22%20slang%3D%22en-US%22%3ERe%3A%20JSON%20Formatting%20Referencing%20Calculated%20Date%20Column%20on%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293416%22%20slang%3D%22en-US%22%3E%3CP%3EMaking%20a%20little%20bit%20of%20progress%20I%20thought%20I%20had%20it%20with%20coming%20across%20the%20date%20operator%20for%20another%20use%20case%2C%20but%20it%20still%20doesn't%20work%20with%20the%20calculated%20field.%20This%20screenshot%20shows%20it%20using%20a%20single%20line%20text%20field%20where%20it%20works%2C%20but%20if%20I%20change%20to%20the%20calculated%20column%20it%20doesn't.%20The%20Phase%20column%20is%20the%20one%20that%20I'm%20applying%20the%20formatting%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20there's%20a%20way%20to%20specify%20the%20calculated%20value%20as%20a%20string%20in%20this%20same%20JSON%20code%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I'm hoping someone can help me so I'm not banging my head on the wall trying to find what I think should be a very simple answer.  Basic scenario:

 

I want to color the text in a choice column based on the date shown in a calculated date column. This works perfectly fine when referencing a date column, but not when referencing a calculated date column. It seems the issue is JSON doesn't read the column as a date value and searching extensively on how to have it read it as a date value I can't find anything. Here is an example of the JSON code:

 

{
"$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": {
"operator": "?",
"operands": [
{
"operator": "<=",
"operands": [
"[$CalculatedDateColumn]",
"@now"
]
},
"#a80000",
""
]
}
}
}

 

It works if the italicized is a date column and I'm guessing there's a way to have JSON treat it as a date column, right?

2 Replies
Highlighted

Making a little bit of progress I thought I had it with coming across the date operator for another use case, but it still doesn't work with the calculated field. This screenshot shows it using a single line text field where it works, but if I change to the calculated column it doesn't. The Phase column is the one that I'm applying the formatting to.

 

Maybe there's a way to specify the calculated value as a string in this same JSON code?

Highlighted
Solution

For anyone else banging their head against the wall trying to get a similar scenario to work I found a solution after coming across this comment in a discussion on GitHub. Changing my calculated column to text instead of date and using the formula to display as a date allows the JSON formatting to "see" the date value to format the Phase column if the date is in the past. My calculated column is looking at multiple date (project phase gates) columns to give the next milestone, but in simple form it works in the attached screenshot.