Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
1 of 3 tasks
lucs17 opened this issue Oct 5, 2018 · 40 comments
Closed
1 of 3 tasks

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

lucs17 opened this issue Oct 5, 2018 · 40 comments
Assignees

Comments

@lucs17
Copy link

lucs17 commented Oct 5, 2018

Category

  • Question
  • 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!

@lucs17 lucs17 changed the title Reference to a Date/Time field Reference to a Date/Time field - Column/View Formatting Oct 5, 2018
@lucs17 lucs17 changed the title Reference to a Date/Time field - Column/View Formatting Reference to a Date/Time field not working (Invalid Date) Oct 5, 2018
@thechriskent
Copy link
Contributor

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?

@thechriskent thechriskent self-assigned this Oct 18, 2018
@thechriskent thechriskent added the ⏳ Waiting for Response Needs a response from the submitter label Oct 18, 2018
@lucs17
Copy link
Author

lucs17 commented Oct 21, 2018

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
Copy link
Contributor

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
Copy link

slmcas commented Oct 25, 2018

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
Copy link

PJS21 commented Nov 12, 2018

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
Copy link

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
Copy link

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

@sympmarc
Copy link

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
Copy link

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
Copy link

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
Copy link

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

screenshot 2018-11-30 at 15 28 28

Showing...

screenshot 2018-11-30 at 15 51 51

@planetparker
Copy link

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
Copy link

VWYankee commented Dec 4, 2018

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

image

@belefevre
Copy link

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
Copy link

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
Copy link

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
Copy link

nilsvbconxion commented Jan 25, 2019

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:
errorviewformatting

Fine with system fields like Created:
errorviewformattingcreated

@thechriskent : any news on this issue?

@martin-braun
Copy link

martin-braun commented Feb 27, 2019

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
Copy link

gristy58 commented Mar 1, 2019

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
Copy link

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
Copy link

gristy58 commented Mar 2, 2019

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
Copy link

gristy58 commented Mar 3, 2019

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

@gristy58
Copy link

any update on when this might be fixed?

@steps39
Copy link

steps39 commented Apr 15, 2019

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
Copy link

gristy58 commented Apr 15, 2019 via email

@steps39
Copy link

steps39 commented Apr 15, 2019

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
Copy link

gristy58 commented Apr 15, 2019 via email

@steps39
Copy link

steps39 commented Apr 15, 2019

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
Copy link

gristy58 commented Apr 16, 2019 via email

@steps39
Copy link

steps39 commented Apr 16, 2019

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
Copy link

steps39 commented Apr 16, 2019

@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
Copy link

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
Copy link

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
Copy link

jsabety commented Apr 28, 2019

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

@JoeAyre
Copy link
Contributor

JoeAyre commented Mar 11, 2020

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
Copy link

hikmatune commented Mar 17, 2020

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
Copy link
Contributor

@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

@hugoabernier hugoabernier removed the ⏳ Waiting for Response Needs a response from the submitter label Jul 24, 2020
@alexeyyanukovich
Copy link

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
Copy link

NasaOK1 commented Jul 19, 2022

This still is causing problems:
image

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.

image
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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests