Wrong Date Calculation - SharePoint

[SP1]

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.

[#2]

@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.

[#3]

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?

[#4 · Accepted solution]

@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.

[#5]

@kalpeshvaghela 

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!

[#6]

@SP1 You are getting "Sorry, something went wrong" error because you are trying to use another column (DueDate) in "calculated value" of "Task" column. This is not supported in SharePoint.

Instead, you have to create a column with type "Calculated". Follow these steps to create a calculated column: 

1. Go to your SharePoint list
2. Click on Add column & select More...
3. It will take you to the classic experience "Create column" page. Here you can selected the Calculated option under "The type of information in this column is".
4. Then enter the formula given above in "Formula" textbox under "Additional Column Settings".
5. Adjust the formula as per your column names. You need to use the column name in format: [Column Name]
6. Click "OK" at the bottom of page to create a new column.

---

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.

[#7]

@ganeshsanap 

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

[#8]

@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.

[#9]

@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