Forum Discussion
Set column value based on another columns date
In Document Library i have a column with a date. This date is filled out by a Power Automate approval flow.
We alse have a second column which has 3 choices. >2 months old, <2 months old, >4 months old.
As i understand sharepoint calculated column can't use TODAY() or NOW(), so i must use conditional formatting?
I need help with JSON formatting to:
- if date in column "Approval Date" is more than 4 months old, set value ">4 months old" - color RED
- if date in column "Approval Date" is more than 2 months old, set value ">2 months old" - color orange
- if date in column "Approval Date" is less than 2 months old, set value "<2 months old" - color green
I could run a flow daily to update this, but i think that is waste of computing resources, when maybe conditional formatting can do the trick?
Optional: If possible they should be the new type of formatting of choice with rounded colores around text?
Column formatting won't update the values in your other column - it will only allow you to display a value based on some conditions. So if you wanted to actually update the second column's value I believe you'd need to run maybe Power Automate or similar.
If you wanted to just display < 2 months old, etc. in the column and color coded (using pills) then you could do something like the JSON below. In my example I have a column called ApprovalDate of type DateTime and a column called Status of type Single line of text. The long numbers used are milliseconds to get you 4 months (120 days which is 10368000000 milliseconds) and 2 months (60 days which is 5184000000 milliseconds).
The limitations with this are that you won't be able to sort, filter or group by Status as that column doesn't actually contain any values.
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "flex-wrap": "wrap", "display": "flex" }, "children": [ { "elmType": "div", "style": { "box-sizing": "border-box", "padding": "4px 8px 5px 8px", "overflow": "hidden", "text-overflow": "ellipsis", "display": "flex", "border-radius": "16px", "height": "24px", "align-items": "center", "white-space": "nowrap", "margin": "4px 4px 4px 4px" }, "attributes": { "class": { "operator": "?", "operands": [ { "operator": "<", "operands": [ "[$ApprovalDate]", { "operator": "-", "operands": [ "@now", 10368000000 ] } ] }, "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont", { "operator": "?", "operands": [ { "operator": "<", "operands": [ "[$ApprovalDate]", { "operator": "-", "operands": [ "@now", 5184000000 ] } ] }, "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-GoldFont", "sp-css-backgroundColor-BgMintGreen sp-field-borderAllRegular sp-field-borderAllSolid sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont" ] } ] } }, "txtContent": "=if([$ApprovalDate] <= @now - 10368000000, '> 4 months old', if([$ApprovalDate] <= @now - 5184000000, '> 2 months old', '< 2 months old'))" } ] }
3 Replies
- Thanks guys
I have understood that calculated columns also doesn't support functions like now() or today().
Thus I have implemented a daily running Automated Flow to calculate and then update the status. Megaoctane in the previous post grant_jenkins has answered the main point quite correctly. For the flow you could have a schedule trigger that runs once a day to update the >4 months old etc.
Rob
Los Gallardos
Microsoft Power Automate Community Super UserColumn formatting won't update the values in your other column - it will only allow you to display a value based on some conditions. So if you wanted to actually update the second column's value I believe you'd need to run maybe Power Automate or similar.
If you wanted to just display < 2 months old, etc. in the column and color coded (using pills) then you could do something like the JSON below. In my example I have a column called ApprovalDate of type DateTime and a column called Status of type Single line of text. The long numbers used are milliseconds to get you 4 months (120 days which is 10368000000 milliseconds) and 2 months (60 days which is 5184000000 milliseconds).
The limitations with this are that you won't be able to sort, filter or group by Status as that column doesn't actually contain any values.
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "flex-wrap": "wrap", "display": "flex" }, "children": [ { "elmType": "div", "style": { "box-sizing": "border-box", "padding": "4px 8px 5px 8px", "overflow": "hidden", "text-overflow": "ellipsis", "display": "flex", "border-radius": "16px", "height": "24px", "align-items": "center", "white-space": "nowrap", "margin": "4px 4px 4px 4px" }, "attributes": { "class": { "operator": "?", "operands": [ { "operator": "<", "operands": [ "[$ApprovalDate]", { "operator": "-", "operands": [ "@now", 10368000000 ] } ] }, "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont", { "operator": "?", "operands": [ { "operator": "<", "operands": [ "[$ApprovalDate]", { "operator": "-", "operands": [ "@now", 5184000000 ] } ] }, "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-GoldFont", "sp-css-backgroundColor-BgMintGreen sp-field-borderAllRegular sp-field-borderAllSolid sp-css-borderColor-MintGreenFont sp-css-color-MintGreenFont" ] } ] } }, "txtContent": "=if([$ApprovalDate] <= @now - 10368000000, '> 4 months old', if([$ApprovalDate] <= @now - 5184000000, '> 2 months old', '< 2 months old'))" } ] }
