Forum Discussion
JSON Code not working when calculating Days Since Received
I am hoping to use a Microsoft List to manage a team's files.
I originally used the following code in my "Days Since Received" column, however, I later learned that this formula would not automatically (dynamically?) update after noticing that the days within the column never changed after being entered:
=TODAY()-[Received (date)]
To rectify this, I found a suggestion on the internet to use a JSON code to try and make it so the MS List automatically updates each day on its own. Unfortunately I am receiving the following error in the Days Since Received column:
=floor((Number(@now)-Number([$Received_x0020_(date)]))/(1000*60*60*24*12)/365*12)
The JSON I am using is as follows:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=floor((Number(@now)-Number([$Received_x0020_(date)]))/(1000*60*60*24*12)/365*12)"
}
I am unsure what's going wrong. This is how I've set it up:
LearningSharepoint what are the dates in your ReceivedDate column? Also, make sure you don't just re-name the column but create a new one as renaming it doesn't change the original internal column name.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
7 Replies
LearningSharepoint try this instead:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=floor((Number(@now)-Number([$Received_x0020_(date)]))/86400000" }I don't know exactly what error you are seeing, so just to add context; Microsoft are rolling out a new list experience and it's giving a bunch of different issues with the json formatting. At first I got a "NaN" on the above, but after adding a new column, somehow it fixed itself. Don't make me try to make sense of it, but just so you know.
Hanne_Lauritzen the error I see is displayed in my original post - it isn't really any error per se, but it just displays the JSON after =floor where the number of days calculated should be:
=floor((Number(@now)-Number([$Received_x0020_(date)]))/(1000*60*60*24*12)/365*12)Thanks for mentioning the changes Microsoft is doing. I created a new column with the JSON to see if that would sort it, and unfortunately it didn't. Any suggestions on how to calculate the difference between TODAY and the date received that updates dynamically, that isn't using JSON?
Thank you.
LearningSharepoint a calculated column will always return a static value so for it to be dynamic you must use JSON; there s no other method. A couple of comments on what you've done so far:
- your column appears to be called Received (date). The parenthesis are special characters which need to be converted in the same way that a space is converted to _x0020_ . I have to say that in my column names I always try to avoid special characters as far as possible as they invariably cause problems. So my preferred column name would be Received or ReceivedDate.
- Your JSON doesn't have these converted characters, you have just used the parenthesis which is incorrect.
- Look at the internal name of the column to get the correct name to use in your JSON. Go to list settings, click on the field and in the url bar the internal name is after the Field=. In my case the internal name of Received (date) is Received_x0028_date_x0029_
The following JSON will then work to dynamically calculate the number of days between Received (date) and now:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=floor((Number(@now)-Number([$Received_x0028_date_x0029_]))/86400000)" }Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
