SharePoint Online Custom Format View issue with @now and UK Date format


Before you read all of this, I do not have a solution. I currently have an open premier support call with Microsoft regarding this, and when this is fixed, I will update the post.

View formatting is a way to change the look of your Lists/Libraries. Using JSON you apply to the list will change the way it looks. The following link takes you to Microsoft Page about Custom Views. https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/view-formatting.

There are two ways to write the JSON, one way is using Abstract Tree Syntax (AST) or you can write in the Excel-style. I will be using the Excel-style.

I have recently started using them for a project I was working on, it was a simple request to change the colour of the row depending on a date column. The rules were:

  • Date equals to today – Leave the row white.
  • If Date is before today – Make the row red.
  • If Date is in the next 7 days – Make the row Orange.
  • If Date is between 7 and 28 days away – Make the row Yellow.
  • More than 28 days – Make the row green.

However, I was noticing that the colours didn’t reflect the correct values. It was only when date changed from the 28th February to the 1st March, the colours changed (still incorrect though) when I realised that the custom formatting didn’t understand UK date formats. Soon as I flipped my Regional Settings for the Locale to be English (United States) the colours displayed correctly.

Example

(Please note I am doing this example on the 8th March 2019)

For this example, I have created a SharePoint List and added an additional Date column. For the purpose of understanding what is going wrong, I have created 2 more text columns called “Date In UK” and “Date in US” where I have put the date values in text for everyone to understand. (e.g A date written like: 03/04/2019, an English person would read this as 3rd April 2019, where an American person would read this as 4th March 2019)

The JSON format I have applied is below, this uses the Excel-Style formatting of using a nested if statement. An excel if statement looks like : =if(logic_test, true, false) When you are doing a nested If statement, the false value, is another if statement. Meaning if 1st logic_test is false, then run the second if statement instead. =if(1st_logic_test, true, if(2nd_logic_test, true, false))

To do the comparison against the current date, you use the @now keyword. If you need to calculate a week date from now you need to add 7 day worth of milliseconds, (7 * 24*60*60*1000 = 604,800,000) @now + 604800000

{
  "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "additionalRowClass": "=if([$Date] == @now,'',if([$Date] < @now, 'ms-fontColor-neutralSecondary ms-fontColor-redDark ms-fontSize-mPlus ms-fontWeight-bold sp-field-severity--blocked',if([$Date] <= @now + 604800000,'sp-field-severity--severeWarning ms-fontColor-neutralSecondary ms-fontWeight-bold',if([$Date] <= @now + 2419000000,'sp-field-severity--warning','sp-field-severity--good ms-fontColor-neutralSecondary'))))"
}

When this JSON is applied to the list, my list looks like the following:

As you can see from the picture above (if the colours come out right on your screen) the formatting is incorrect.

Item 1 – is Red

Item 2 – is Yellow

Items 3 to 7 are Green.

Remember I’m doing this demo on the 8th March 2019, which should show:

Items 1 to 3 – in Red (Before today)

Items 4 – White (as it’s today’s date)

Item 5 – Orange (In next 7 days)

Item 6 – Yellow (In next 7 to 28 days)

Item 7 – Green (Over 28 days)

So why do I have Item 1 red, and Item 2 yellow, but the rest green. This is because the date is being read in by the formula the wrong way round as an American date. The 4th column “Date in US” is the way the date is being read in.

Item 1 – being read in as the 3rd January, which is before the 8th March – Red

Item 2 – being read in as the 3rd April, which is more than 7 days after the 8th March – Yellow

Items 3 to 5 – being read in as the 3rd July onwards, which is more than 28 days after the 8th March – Green.

Items 6 to 7 – Would have invalid dates in American date format because there is not a 20
or 18 month. It is green because of the last if statement that says If less than 28 days, (true) display yellow, else (false) display green.

Changing the Regional Settings Locale.

I will now go to my Regional Settings and change the Locale. This can be found in site Settings, and under Site Administration select Regional Settings.

Change the Local from English (United Kingdom) to English (United States)

When I go back to my list, the colour formatting looks very different, but correct. (Well almost)

Apart from Item 4 the 8th March 2019 which should be white because it is today’s date, all the colours are now correct according to the JSON formula.

Conclusion

I have tested this same formula on 3 different tenants, and the same happens every time. Therefore, I’m certain it isn’t a problem with my tenant. It doesn’t seem to be just the UK date format either, it affects any date when the format isn’t in the American format of MM/DD/YYYY.

After searching on the Web trying to find a solution, I did come across this github issue https://github.com/SharePoint/sp-dev-list-formatting/issues/92 which has been opened since 5th October 2018. This issue is around calculated columns using dates but reading through the comments it does seem that people are saying they can get it working when using the US date format.

Until Microsoft addresses this bug, using conditional formatting on date columns is pointless, unless you are using the US Date format.

I have yet to try to write the Formula using the Abstract Tree Syntax(AST) way.