Column formatting shows different date values for different users

Question

Hi folks, 

We crated a custom JSON to format date with a specific format but for some users the date shown is one day ahead. The column formatting is supposed to the date in the format DD MMM YYYY. 

So the problem is: when a date is saved as 15/12/2020, for most users it shows: 15 Dec 2020 but for some users it shows 14 Dec 2020. 

The Site Regional settings is in EN-US. Time Zone: UTC+1. The JSON is not using locale functions. 

To show the day for the date we use getDate([$mydatefield]

As I understand it should use the regional settings of the site to show the date, or am I missing something? 

Best regards, 

Sébastien.

Answer 1

Hi Sebastien,

I apologize for all the trouble it has caused and sorry about your time.

As per your description, you use “custom JSON to format date” in SharePoint online list but it shows different date format for different users.

May I know which custom JSON format you are using, could you please share with us screenshot it will help for us to narrow down your issue.

To change date format on a list to DD-MMM-YY, you can follow the below steps:

1. Go to your list, where you would like to see the formatted date and time.
2. Currently, it displays SharePoint default date and time format.

3.Now, we would like to display date DD-MMM-YY, as shown in the picture given below. We have added a new column to display the format.

4.To achieve this, we need to add a calculated column and use the sample code given below.

Meanwhile, please try to double verify Time zone setting from different user side via go to setting> site setting> Regional setting. Also please confirm does issue happen to specific list in SharePoint online or all list.

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 2

Thanks for your answer. 

It works indeed properly with a calculated column but it means we would need to duplicate all date fields. 

The custom JSON used is as follow: 

{

    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

    "elmType": "span",

    "txtContent": "=getDate([$ADateandTime]) + ' ' + if(getMonth([$ADateandTime])==0,'Jan',if(getMonth([$ADateandTime])==1,'Feb',if(getMonth([$ADateandTime])==2,'Mar',if(getMonth([$ADateandTime])==3,'Apr',if(getMonth([$ADateandTime])==4,'May',if(getMonth([$ADateandTime])==5,'Jun',if(getMonth([$ADateandTime])==6,'Jul',if(getMonth([$ADateandTime])==7,'Aug',if(getMonth([$ADateandTime])==8,'Sept',if(getMonth([$ADateandTime])==9,'Oct',if(getMonth([$ADateandTime])==10,'Nov','Dec')))))))))))

• ' ' + getYear([$ADateandTime])",

    "style": {

      "display": "=if(length([$ADateandTime] < '4', 'none', 'block')",

      "padding-left": "3px"

    }

  }

The users who are seing a different date have actually a different Time Zone set in their Windows 10 session than the other users. So it appears that the getDate function is using the local machine TimeZone to convert the date value. Is there another function that we could use to use the time zone of the SP Site instead?

Answer 3

Hi Sebastien,

Thanks for sharing updates with us. We feel sorry to hear that you meet such an issue. However, we appreciate your understanding.

Since, you mentioned, “The users who are seing a different date have actually a different Time Zone set in their Windows 10 session than the other users. “please try to see the result via using different pc e.g., configure affected user account under non problematic users pc.

Meanwhile, I’d like explain that in fact we can set different time zone and region for see  each SharePoint site, you can check this this article to see more information: Change regional settings for a site

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad

Answer 4

Hi, 

Thanks for your answer. 

I could reproduce the issue on my machine by changing the Time Zone in my Windows settings so it's not related to the user. 

And the Regional Settings of the site are correctly set (UTC+1). But as explained above the getDate function seem to return the date in the Windows regional settings, not the site regional settings. Is that the expected behaviour? The documentation is not very clear about that.

Best regards,

Answer 5

Hi Sebastien,

We appreciate that you spent your precious time doing those and shared updates with us. I do understand the inconvenience it has made and apologize for it.

As far as I know using calculated column ad date/time column the date format is set in site regional settings.

As our forum focus on how to deal with the out-of-box issue of SharePoint, we are really sorry for couldn't provide a better suggestion for you. Since use are using custom formatting JSON code to format Date column and it seems using this getDate function seem to return the date in the Windows regional settings. As we have limited resources and very little knowledge about  JSON code, we sincerely recommend you raise questions in Microsoft Q&A – SharePoint Online. Questions about programming, development, and so on can all get answered there. Experts there forum would conduct in-depth research on the problem you encountered and provide you with more professional assistance.

I appreciate your patience and understanding.

Best Regards

Waqas Muhammad