Reference to a Date/Time field not working (Invalid Date)

[lucs17]

Category

• [ ] Question
• [x] Bug
• [ ] Enhancement

Expected or Desired Behavior

When I reference to a Date/Time field in column oder view formatting (like "txtContent": "[$Created]"), I expect to see the date/time in local format (in this case the Europe format: DD.MM.YYYY).

Observed Behavior

List field shows "Invalid Date". Also when I use toLocaleDateString().

Steps to Reproduce

Reference to a Date/Time field in row oder view formatting by "[$FieldName]". "@currentField" works as expected.

Thank you for your support!

[thechriskent]

I'm not seeing an issue with using the toLocaleDateString() and Created like this:

"txtContent": "=toLocaleDateString([$Created])"

Is this an issue you are still having?

As for the locale string, this function just uses the JavaScript toLocaleDateString method for Date objects. You can verify if the values match by opening your developer tools (F-12 in most browsers) and pasting the following in the console:

var myDate = new Date(); myDate.toLocaleDateString();

Does your date show in the expected format?

[lucs17]

Dear Chris,

thank you for your feedaback. When I create a list without management of content types (so only list fields), the behaviour is exaclty like this as you describes. But when I add a content type (custom or a predefined one), the behaviour of date/time fields change as I have described in my first post.

As a workaround I can create a calculated field (with text output) and format the date field with this formula: =TEXT([DateField];"YYYY-MM-DD"). Then it works like expected (see also https://www.w3schools.com/js/js_date_formats.asp).

So it looks like sharepoint is save the date field in a by JavaScript "unreadable" format?

Is it possible for you to reproduce this?

Looking forward to hear from you.

• Philipp

[thechriskent]

I am unable to reproduce this. I added a custom content type with a date field to a list and was able to apply view formatting without issue referencing this date. The format looked exactly as expected. Could you post your format json?

Also, although calculated columns work very well for standard displays, their values are not accessible through list formatting. Attempting to pull values from a calculated field will not work and will show Invalid Date instead. Are you using a calculated column for this?

[slmcas]

I am also having the Invalid Date issue, it seems related to the locale of the site. I am using "txtContent": "=toLocaleDateString([$Closingdate])" in the view format. If the site locale is English (United Kingdom) it displays Invalid Date. If I change the site's locale in Regional Settings to English(United States), the date is displayed.

[PJS21]

I am also having the invalid date issue. I am using English (Australia). If the date is in a valid US format, it displays the date correctly. E.g, if i set the date to 3 December 2018), it will display as 3/12/2018. If I use 24 December 2018, it will display "Invalid Date"

[phillipharding]

I also have this issue, using a list view formatter for Date fields, but only with IE 11.

Using a toLocaleString() expression such as below, when viewed with IE11, "Invalid Date" is rendered for the date field, but as expected when viewed with Edge, Chrome, Safari

{
   "elmType": "div",
   "attributes": {
      "class": "ms-fontSize-s"
   },
   "style": {
      "line-height": "1.5em",
      "margin-bottom": "8px"
   },
   "txtContent": "='Modified by ' + [$Editor.title] + ', ' + toLocaleString([$Modified])"
}

Using Date Field with toLocaleString() - Internet Explorer 11 Compatibility Issue

[JulesAnime]

Calculated Date Fields do not seem to be displayed in Formatted Views, although can be used in formulas

[sympmarc]

I got a question on my blog similar to this. In the question, Paula said this used to work with a calculated date column but no longer does. I tested with a calculated column with the formula "=Created+10"

{
    "elmType": "span",
    "txtContent": {
        "operator": "toLocaleDateString()",
        "operands": [
            "@currentField"
        ]
    }
}

I can reproduce this. I tried the "modern" modern approach, and this works:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=toLocaleString([$Created])"
}

But this does not:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=toLocaleString([@currentField])"
}

My guess here is that calculated date columns no longer work with column formatting. Known issue? No idea.

[phillipharding]

After further investigation, the story gets wierder and muddier

Using the Modified date field

                  {
                    "elmType": "span",
                    "txtContent": "[$Modified]"
                  }

In Chrome, Safari, Edge this works but not in IE11 which displays Invalid Date

Using a custom date field (Date & Time or just Date)

                  {
                    "elmType": "span",
                    "txtContent": "[$Contractstartdate]"
                  }

In Chrome, Safari, Edge this doesn't work displaying Invalid Date, but works in IE11

Using the Created date field

                  {
                    "elmType": "span",
                    "txtContent": "[$Created]"
                  }

In Chrome, Safari, Edge this doesn't work displaying Invalid Date, but works in IE11

The site is a Group connected site, the locale is "English - United Kingdom", and the customizer is a list view formatter, see below;

{
  "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
  "hideSelection": false,
  "hideColumnHeader": true,
  "rowFormatter": {
    "elmType": "div",
    "attributes": {
      "class": "ms-borderColor-neutralLight"
    },
    "style": {
      "box-sizing": "border-box",
      "border-width": "1px",
      "border-left-width": "1px",
      "border-style": "solid",
      "border-left-color": "#107C10",
      "margin": "0 0 10px 0",
      "padding": "0 0 0 15px",
      "display": "block"
    },
    "children": [
      {
        "elmType": "span",
        "attributes": {
          "class": "ms-fontWeight-semibold"
        },
        "txtContent": "[$Title]"
      },
      {
        "elmType": "span",
        "attributes": {},
        "txtContent": "=',   Dateonlyfield:'+toString([$Dateonlyfield])"
      },
      {
        "elmType": "span",
        "attributes": {},
        "txtContent": "=',   Datetimefield:'+toString([$Datetimefield])"
      },
      {
        "elmType": "span",
        "attributes": {},
        "txtContent": "=',   Modified:'+toString([$Modified])"
      },
      {
        "elmType": "span",
        "attributes": {},
        "txtContent": "=',   Created:'+toString([$Created])"
      }
    ]
  }
}

[VWYankee]

While I'm unsure if this will help for the scenario here I thought I'd add that the comment from lucs17 helped me get some formatting working on a SharePoint list that I had been struggling with for much longer than I'd like. I'm a long time SharePoint Architect, but very new to using JSON for column formatting. I wasn't finding anything around using a calculated date field as an operand, so this was a tremendous help for me! I was very close to just creating a mundane workflow to copy the calculated value into a text field on every item change!

As Marc eluded to, it seems using calculated date columns doesn't work, but they will if they're calculated text displayed as a date. Thank you!!

[phillipharding]

@sympmarc @VWYankee using a Calculated field [as TEXT], while not ideal, seems to work in Chrome, Safari, FireFox, Edge and IE11...

Showing...

[planetparker]

Not sure if this helps, but I found another workaround for the "Invalid Date" issue. In my case, once I removed it from the underlying view, the issue went away. So I referenced it in my view formatting JSON, but it's unchecked (not displayed) in the view.

[VWYankee]

I found one tiny issue that I know will be reported to me if I can't roll out without it remedied. I'm wondering if someone has some insight for. As mentioned in my joyous discovery, I have my calculated column (Next_x0020_Milestone) as text but in the calculation format it as a date. Here's the section I'm having trouble with. When the Next Milestone date is today it seems to think @now is greater than it so it formats the red. If I reference a date column instead it works as expected - turns red only when today is actually greater than (not when equal to). I even tried adding in a day from referencing MS's document on column formatting, but couldn't seem to find a way for that to work either. It is working with the dates in both directions with exception of the same day as today (@now).

[belefevre]

I also have this issue with my SPonline. My SP is in French with date format (DD/MM/YYYY) and I have this issue when day date is bigger than 12. { "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "=Date(@currentField)" } For your info

[AndrewCarterBreakwaterIT]

I have found that if I revert my Regional Settings back to United States from United Kingdom the dates correctly display. However, if I change to UK as the regional setting I get Invalid Date..

[egidiocs]

Calculated Date Fields do not seem to be displayed in Formatted Views, although can be used in formulas

A calculated column must be in the view to be used in formatting.

[egidiocs]

The same here, site settings:

• Language: 1033 (english)
• Locale: 1046 (Portuguese Brazil)

Example

"DocDate": "2018-10-29T09:15:21Z" 
  =anyDateOperator([$DocDate]) returns 
      "Invalid Date" (Firefox)
      "11/19/2018" (Chrome)
     "Mar 30 2018" (IE 11)

anyDateOperator => Date() toLocaleDateString() toLocaleString()

[nilsvbconxion]

Same issue here! I use this JSON for the formatting: { "elmType": "span", "txtContent": "[$MyDate]" }

• SharePoint site locale: Dutch (Belgium)
• Browser: Chrome
  • Browser display language: English US
  • Browser locale: English US
• Windows locale: Dutch (Belgium)

I can also confirm the findings of @phillipharding ! Displaying system fields like Created or Modified render just fine, but custom Date-fields don't. Very annoying!

I also noticed something strange: when you load the formatted view that displays the "Invalid Date" text, then switch back to a non-formatted view and switch back once more to the formatted view: everything is fine. Note that you can't refresh the page when doing this. If you do, then the formatted view has the "Invalid Date" text again.

Problem with custom date-column:

Fine with system fields like Created:

@thechriskent : any news on this issue?

[martin-braun]

I'm afraid I have to confirm that date/time fields are very broken and we have issues working with them in the list formatters right now.

Please check this Row Formatter using a custom date time field on a custom list:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "additionalRowClass": "=if([$MyField] < @now, 'sp-field-severity--warning','')"
}

All dates that are in the past make the rows turn yellow, this works.

As soon as I try to do the same with the $Created field it does not work:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "additionalRowClass": "=if([$Created] < @now, 'sp-field-severity--warning','')"
}

Here all rows should turn yellow, but none of them do.

Moving on, to a document library of a german sharepoint online instance. Inserting the first JSON into a row formatting of such document library while having a date time field called $MyField it does not work at all on IE / Firefox. On Chrome, it seems to act like the day is the month and the month is the day:

The german date 01.03.2019 is the 1st March of 2019, but the list formatting interprets it like the 3rd January of 2019. We have checked the API returns the correct ISO date, the configuration of both custom fields from the list and document library are the same.

Using the 2nd JSON on the document library row formatting (using $Created instead of $MyField as variable) does not work at all, no rows turn yellow on all browsers.

The issue on the custom fields of the document library is really devastating for us right now. How we are supposed to explain our client that row formatting does not work properly on document libraries? Is there any way to make date time comparing on custom fields work properly on document libraries?

[gristy58]

I have the same issue as well glad not alone

Chrome/Edge - InvalidDate and obviously did not work in calculations IE - It would make up a random date in the future rather than Invalid - useless for both display/calculations

did anyone else find a workaround? The only thing i could do was copy the text with a workflow to use the date as a display, but converting that back to a date for calculations gives the same as the original column.

[martin-braun]

did anyone else find a workaround? The only thing i could do was copy the text with a workflow to use the date as a display, but converting that back to a date for calculations gives the same as the original column.

For some reason, we don't have problems showing the correct date, we just struggle to use the custom date field in the document library for calculations (conditional styling of rows).

We ended up building a flow that puts the total milliseconds into an extra field. After that you can compare it with Number(@now), however you have to take the difference between both sides into account, because the flow would set the total milliseconds since 1900-01-01, while Number(@now) outputs the milliseconds since the Unix epoch (1970-01-01).

Hopefully this helps you somehow.

[gristy58]

In my scenarios I cannot have any flows at the moment for this solution .

What I have done is set the region to USA on my custom rendered view it is displaying in dd/mm/yyyy format somehow. On normal list view o have formatted the column to rearrange the date. This means at the moment the only US date is shown when editing/adding a list item.

[gristy58]

this evening things are working as expected will keep monitoring as it appears to be random.

[gristy58]

any update on when this might be fixed?

[steps39]

I have just discovered List Formatting in Sharepoint and may finally be a way of stopping colleagues producing endless spreadsheet, but I guess this issue still exists,

I simply want to highlight completion dates missed, but the json below highlights almost all rows whether dates missed or not: { "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json", "additionalRowClass": "=if([$Target_x0020_Completion_x0020_Da] <= @now, 'sp-field-severity--severeWarning', ''" }

I hope it will be fixed soon.

[gristy58]

Yup if you have dd/mm format then it’s still not working properly which sucks

On Mon, 15 Apr 2019 at 11:53 am, steps39 notifications@github.com wrote:

I have just discovered List Formatting in Sharepoint and may finally be a way of stopping colleagues producing endless spreadsheet, but I guess this issue still exists,

I simply want to highlight completion dates missed, but the json below highlights almost all rows whether dates missed or not: { "schema": " https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json ", "additionalRowClass": "=if([$Target_x0020_Completion_x0020_Da] <= @now https://github.com/now, 'sp-field-severity--severeWarning', ''" }

I hope it will be fixed soon.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/SharePoint/sp-dev-list-formatting/issues/92#issuecomment-483432922, or mute the thread https://github.com/notifications/unsubscribe-auth/AkBODTz1ELjT1ngj4XqJYvh-efe6IK81ks5vhPTygaJpZM4XKec7 .

-- Regards

Chris Grist

[steps39]

Thanks for quick response, sorry should have said I am using UK format so dd/mm/yyyy, as you say it really sucks, being a newbie to json Sharepoint I have discovered that Sharepoint variable names aren't what I expected (sorted out spaces x0020 and truncation of field Date to _Da and even Status being Status0), only to discover that simple date comparisons just don't work. Best wishes, Simon Gibbon

Yup if you have dd/mm format then it’s still not working properly which sucks -- Regards Chris Grist

[gristy58]

What I did was set the locale to United States and then got end users to set their own Timezone / locale in their user profile - not ideal but seemed to work

On Mon, 15 Apr 2019 at 12:01 pm, steps39 notifications@github.com wrote:

Thanks for quick response, sorry should have said I am using UK format so dd/mm/yyyy, as you say it really sucks, being a newbie to json Sharepoint I have discovered that Sharepoint variable names aren't what I expected (sorted out spaces x0020 and truncation of field Date to _Da and even Status being Status0), only to discover that simple date comparisons just don't work. Best wishes, Simon Gibbon

Yup if you have dd/mm format then it’s still not working properly which sucks -- Regards Chris Grist

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/SharePoint/sp-dev-list-formatting/issues/92#issuecomment-483435041, or mute the thread https://github.com/notifications/unsubscribe-auth/AkBODbzYZ1EqQjGaMoWiinTtmKTjiQayks5vhPbAgaJpZM4XKec7 .

-- Regards

Chris Grist

[steps39]

I am not a collection administrator, just administrator for my site. Under Site setting, the Locale is set as English(United States), but I still seem to have the issue and I don't have authority to alter things at this level. Date comparison almost seems to work if I compare 2 date columns, but comparison with @now gives silly results.

What I did was set the locale to United States and then got end users to set their own Timezone / locale in their user profile - not ideal but seemed to work -- Regards Chris Grist

[gristy58]

What if you do toLocale(@now)

On Mon, 15 Apr 2019 at 1:17 pm, steps39 notifications@github.com wrote:

I am not a collection administrator, just administrator for my site. Under Site setting, the Locale is set as English(United States), but I still seem to have the issue and I don't have authority to alter things at this level. Date comparison almost seems to work if I compare 2 date columns, but comparison with @now https://github.com/now gives silly results.

What I did was set the locale to United States and then got end users to set their own Timezone / locale in their user profile - not ideal but seemed to work -- Regards Chris Grist

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/SharePoint/sp-dev-list-formatting/issues/92#issuecomment-483453284, or mute the thread https://github.com/notifications/unsubscribe-auth/AkBODS3DAYK1AGGbI-a5ScYKFuGglOWEks5vhQikgaJpZM4XKec7 .

-- Regards

Chris Grist

[steps39]

What if you do toLocale(@now) -- Regards Chris Grist Thanks for the suggestion - function toLocale reports unknown token, but it got me thinking - so I tried Date(toLocaleDateString(@now)) which returns the same highlightings as @now alone - so I am left feeling that I am up against a Sharepoint error as: =if(Date([$Target_x0020_Completion_x0020_Da]) <= Date(toLocaleDateString(@now)) , 'sp-field-severity--severeWarning', '') Highlights 2/4/2019, 10/09/2018, 05/08/2018, 04/07/2018 but not 29/09/2018, 30/08/2018 ..... I can't see any logic in this. I also haven't spotted the documentation which explains how Sharepoint shortens field names to become variable names - so for me "Target Completion Date" is $Target_x0020_Completion_x0020_Da which I found from the page source. Best wishes, Simon

[steps39]

@gristy58 following your tips combined with searching led me to a similar issue - https://social.technet.microsoft.com/Forums/en-US/21712788-fafa-4898-9cee-897d1b3afb88/modern-list-view-formatting-with-uk-dates?forum=onlineservicessharepoint, so I have finally manged to fix with a hack - created a USDueDate calculated date only column - =TEXT([Target Completion Date],"mm/dd/yyy") - then rewrote my json: { "schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json", "debugMode": true, "additionalRowClass": "=if(Date([$USDueDate]) <= Date(@now) && ([$Status0]!='3. Complete' && [$Status0]!='4. Superceeded'), 'sp-field-severity--severeWarning', '')" } Now just need to find a beginner's guide to this, thought I had found a useful Microsoft page with a nice little pdf button, but now have 6924 pages - I guess the complete developer reference - difficult to find the obvious like && is and, but can't find the equivalent for or. I guess installing Microsoft Visual Studio will also help with autocompletion / hints etc.. Hope Microsoft fix it so that Sharepoint just uses consistent date conventions in calculations, no reason why display should have any influence on date calculations. Best wishes, Simon Gibbon

[VWYankee]

I too was a bit frustrated at the lack of information in Microsoft documentation. While there's a lot there's nothing to just say what the operators are and do. There's a page that lists all the operators but doesn't even say what they are - how is that helpful?? Thankfully, a colleague well versed in JS let me know that the oprators are the same as C operators. I then was able to find this C Operators page I have saved a link in my OneNote reference to. I wanted to know the same - "Or" is double pipe - ||

[hristochr]

My very similar case under Chrome:

• custom date field renders "Invalid date", locale is Dutch/Belgium
• created & modified do not return an error but do not render either
• a calculated field returning text and made of Title + TEXT([MyDate],"dd.MM.yyyy") does not render at all.

[jsabety]

Same issue here for UK regional settings and custom Date field. Date appears correctly in other views and quick edit.

[JoeAyre]

Same issue. The 'toLocaleDateString' function does not alter the appearance of a date string. Frustrating as this was working a few months ago and now doesn't. Whatever we do, we are stuck with the (in my opinion, illogical) US date formatting. Hopefully this will be fixed very soon as currently it renders our solution unusable.

[hikmatune]

I created an additional date field CreatedDate and set default to now. The code below works now

"txtContent": "=getDate([$CreatedDate]) + '/' + Number(getMonth([$CreatedDate])+1) + '/' + getYear([$CreatedDate])"

I then used pnp to hide the field from users

Connect-PnPOnline https://tenant.sharepoint.com/sites/sitename/

$ctx = Get-PnPContext
$field = Get-PnPField -Identity "CreatedDate" -List "Bulletin Board"

$field.SetShowInNewForm($false)
$field.SetShowInEditForm($false)
$field.SetShowInDisplayForm($false)
$field.Update()
$ctx.ExecuteQuery()

[hugoabernier]

@lucs17 did @hikmatune's and other suggestions help you solve this issue? I'll go ahead and close the issue.

If you're still experiencing this issue, please don't hesitate to open a new issue

[alexeyyanukovich]

As a workaround you can convert a date field to string, swap the day and the month fields, then use the string as a Date Date(substring(toString[$Modified], 3, 5) + '/' + substring(toString[$Modified], 0, 2) + substring(toString[$Modified], 5, 21))

[NasaOK1]

This still is causing problems:

I wanted to create an action Button that sets a DateColumns Value to today's date when pressed.

German locale (dd.MM.yyyy), Edge browser.

The error tells me that a dd.MM.yyyy. date is expected, if I set the action to set the Value of a Text field, it's the correct format. However if I hardcode a MM.dd.yyyy date into the action, it will work. If I change the sites regional settings to US, the @now still produces a dd.MM.yyyy date, but the error I posted above, requests a MM/dd/yyyy date.

SharePoint seems like it needs a MM.dd.yyyy date passed to it no matter what. Unfortunately I havent found any "format datetime" style function for the action button syntax, unlike MS has in PowerApps, LogicApps, Flow, etc