Forum Discussion
Solved
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": ...
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.
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.
Dear kalpeshvaghela
Thank you very much indeed for taking out time for me and helping me.
I tried your code - still I get the same problem!
- Today is showing Yesterday
- Tomorrow is showing Today
- 3 Days is showing 1 day less!
Please see the screenshot below -
Does that mean - My SharePoint has issues?
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.
Once again, Thank You very much indeed for your help and advice. I really appreciate your help.
I tried your Concatenate function - don't know why - but I always get this error.
But I understood your point... if you have some advice on this please share.
Also, I did try on a different organization SharePoint but the same Country - still gave me the same error with your code...
I don't know why but I am in UK time zone and we use DD/MM/YYYY - may be this is creating problem!
