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.
kalpeshvaghela
Aug 08, 2022Steel Contributor
You can try below 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',''))))) + ')'"
}
For me it's working fine. See below screen shot (consider 08-Aug-2022 as today's date):
Hope it will help to you. If so then please mark it as answer or like it.