Forum Discussion
SP1
Aug 06, 2022Copper Contributor
Wrong Date Calculation - SharePoint
My Past - works fine My Today shows = Yesterday My Tomorrow show = Today My 2+ Days.... shows = One day ahead! Please see the screenshot: Code - "txtContent": ...
- Aug 08, 2022
May be because of time zone issue.
You can also try to create Calculated column instead of SharePoint JSON column formatting.You can use below formula for SharePoint Calculated column, in my case it gives correct result now.
=CONCATENATE("(",IF(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")=0,"Today",IF(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")=-1,"Yesterday",IF(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")=1,"Tomorrow",IF(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")>1,CONCATENATE(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")," days"),IF(TEXT(DueDate,"mm/dd/yyyy")-TEXT(NOW(),"mm/dd/yyyy")<1,CONCATENATE(TEXT(NOW(),"mm/dd/yyyy")-TEXT(DueDate,"mm/dd/yyyy")," days ago"),""))))),")")
Below is the output which I am getting now.
Hope it will helpful to you. Please mark and like this answer as best response if it works for you so that it can help to other people.
SP1
Aug 08, 2022Copper Contributor
Dear Ganesh,
Thank you very much for your advice, it did indeed help a lot.
I figured out - kalpeshvaghela =CONCATENATE formula is based on mm/dd/yyyy - I believe Tenant in India uses the same as USA Tenant. But UK Tenants use dd/mm/yyyy.
So I replaced mm/dd/yyyy with dd/mm/yyyy in CONCATENATE formula
it WORKED!! - please see the screenshot below:
But then I applied kalpeshvaghela very first formula -
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "='(' + if(floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) == 0,'Today',if(floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) == -1,'Yesterday',if(floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) == 1,'Tomorrow',if(floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) > 1,floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) + ' days',if(floor((Number([$DueDate])-Number(@now))/(1000*60*60*24)) < 1,floor((Number(@now)-Number([$DueDate]))/(1000*60*60*24)) + ' days ago',''))))) + ')'"
}
Result :
Same - Past works fine but Present and Future - all gets wrong... please see the screenshot below
This makes me think - possibly its because I am using UK Tenant and you guys are USA tenants - so something is wrong in the code and something it's not doing correctly when the date format is dd/mm/yyyy
So, if you guys have any ideas, please do share...
Once again, many thanks for sharing your idea and advice.
Best wishes,
Sanjay
ganeshsanap
Aug 09, 2022MVP
SP1 Check if toLocaleDateString() function in JSON formatting helps to convert both dates in same format & then apply your logic.
Documentation: JSON formatting - operators
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
- SP1Aug 09, 2022Copper Contributor
ganeshsanap and kalpeshvaghela
Found the root cause of the wrong date calculation of the problem -
e.g. (floor(Number(Date([$DueDate]) - Date(@now))/86400000)+1)==0,'Today'
The value is never = 0
its 0.283.. so the FLOOR value its rounding up is = -1 = hence its always Yesterday
The problem here is - Floor is not able to give the right number - hence all these errors