Forum Discussion

SP1's avatar
SP1
Copper Contributor
Aug 06, 2022

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": "='(' +
if(floor(Number(Date([$DueDate]) - Date(@now))/86400000)==0,'Today',

if(floor(Number(Date([$DueDate]) - Date(@now))/86400000)==1,'Tomorrow',

if(floor(Number(Date([$DueDate]) - Date(@now))/86400000)==-1,'Yesterday',

if(floor(Number(Date([$DueDate]) - Date(@now))/86400000)>=2,floor(Number(Date([$DueDate]) - Date(@now))/86400000) + ' days',

if(floor(Number(Date([$DueDate]) - Date(@now))/86400000)<=2,floor(Number(Date(@now)-Date([$DueDate]))/86400000) + ' days ago',''))))) + ')'",

 

Can you please help me fix this code?

 

I would be really grateful to you all.

  • SP1 

     

    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 

     

    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.

    • SP1's avatar
      SP1
      Copper Contributor

      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?

       

       

      • kalpeshvaghela's avatar
        kalpeshvaghela
        Steel Contributor

        SP1 

         

        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.

         

         

Resources