{"action":"created","issue":{"url":"https://api.github.com/repos/dfinke/ImportExcel/issues/499","repository_url":"https://api.github.com/repos/dfinke/ImportExcel","labels_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/499/labels{/name}","comments_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/499/comments","events_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/499/events","html_url":"https://github.com/dfinke/ImportExcel/issues/499","id":386813793,"node_id":"MDU6SXNzdWUzODY4MTM3OTM=","number":499,"title":"Group Data in Pivot","user":{"login":"astapelfeld","id":35689820,"node_id":"MDQ6VXNlcjM1Njg5ODIw","avatar_url":"https://avatars2.githubusercontent.com/u/35689820?v=4","gravatar_id":"","url":"https://api.github.com/users/astapelfeld","html_url":"https://github.com/astapelfeld","followers_url":"https://api.github.com/users/astapelfeld/followers","following_url":"https://api.github.com/users/astapelfeld/following{/other_user}","gists_url":"https://api.github.com/users/astapelfeld/gists{/gist_id}","starred_url":"https://api.github.com/users/astapelfeld/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/astapelfeld/subscriptions","organizations_url":"https://api.github.com/users/astapelfeld/orgs","repos_url":"https://api.github.com/users/astapelfeld/repos","events_url":"https://api.github.com/users/astapelfeld/events{/privacy}","received_events_url":"https://api.github.com/users/astapelfeld/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2018-12-03T13:18:24Z","updated_at":"2018-12-05T10:53:55Z","closed_at":null,"author_association":"NONE","body":"Hello,\r\nis there any way to group data in a pivot table? In Excel -> http://www.exceltoxl.com/index.php?p=264&more=1&c=1&tb=1&pb=1\r\n\r\nThanks!\r\n\r\nAl"},"comment":{"url":"https://api.github.com/repos/dfinke/ImportExcel/issues/comments/444443959","html_url":"https://github.com/dfinke/ImportExcel/issues/499#issuecomment-444443959","issue_url":"https://api.github.com/repos/dfinke/ImportExcel/issues/499","id":444443959,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0NDQ0Mzk1OQ==","user":{"login":"jhoneill","id":16209593,"node_id":"MDQ6VXNlcjE2MjA5NTkz","avatar_url":"https://avatars1.githubusercontent.com/u/16209593?v=4","gravatar_id":"","url":"https://api.github.com/users/jhoneill","html_url":"https://github.com/jhoneill","followers_url":"https://api.github.com/users/jhoneill/followers","following_url":"https://api.github.com/users/jhoneill/following{/other_user}","gists_url":"https://api.github.com/users/jhoneill/gists{/gist_id}","starred_url":"https://api.github.com/users/jhoneill/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jhoneill/subscriptions","organizations_url":"https://api.github.com/users/jhoneill/orgs","repos_url":"https://api.github.com/users/jhoneill/repos","events_url":"https://api.github.com/users/jhoneill/events{/privacy}","received_events_url":"https://api.github.com/users/jhoneill/received_events","type":"User","site_admin":false},"created_at":"2018-12-05T10:53:55Z","updated_at":"2018-12-05T10:53:55Z","author_association":"CONTRIBUTOR","body":"No,  we insert datestime fields as numbers with formatted as \"local date & time\". But we don't infer dates from string fields.  \r\n\r\nThere is the usual thing with date formatting - do you write least significant first (Most countries), so  2/9/18 is Second day of September 2018.    Least significant last 2/9/18 is 18th  day of September 2002 (Japan) or least significant in the middle 2/9/18 is 9th day of February 2018 (US only). \r\n\r\n`[datetime]<string>` is invariant and uses US format - every computer running the code above will produce the same results - your last 3 rows, 11th, 9th and 18th of Feb come into excel as those dates and display on my machine (with Rest of world date format) as \r\n![image](https://user-images.githubusercontent.com/16209593/49505010-bd425000-f872-11e8-8d0a-5394cbb2e841.png)\r\n\r\nBut if that is Boston Lincolnshire, not Boston Mass, that's not how the data would be typed.  But if I export it as a CSV it uses local date format \r\n![image](https://user-images.githubusercontent.com/16209593/49505159-11e5cb00-f873-11e8-8d08-863c499a93ac.png)\r\n\r\nSo I read that CSV and try to process it with your script … oh dear … \r\n\r\n`[convert]::toDateTime(<string>) ` will use local culture, so now the script produces different things on your computer and mine, or even on the same sever depending who logs on. But it will correctly import something which was created with local date format outside the US. If US and Europe swap data we can use \r\n`[convert]::ToDateTime(\"2/9/18\",[cultureinfo]::GetCultureInfo(\"En-uS\"))` or one of the parse methods of date time, to smooth things out. \r\n\r\nThe same problem exists on output \r\n```\r\n>$d = [datetime]\"09/02/18\"\r\n> $d \r\n02 September 2018 00:00:00\r\n```\r\n\r\nYou'd expect `\"date is $d \"`  and` \"date is {0} \" -f $d `   To be the same , right ?  Not if the script runs in Europe \r\n\r\n```\r\n>\"date is $d \"\r\ndate is 09/02/2018 00:00:00 \r\n\r\n>\"date is {0} \" -f $d \r\ndate is 02/09/2018 00:00:00 \r\n```\r\n\r\nIs `$d.toString()`    local or invariant ?   Whichever conversion one picks, it will be wrong enough of the time to be a problem. So **my view** is whatever feeds the data into Export-Excel needs to be smart about putting passing dates and times as dateTime data types. Hopefully whoever writes / runs the script knows what format the date is in and which conversion to use.\r\n\r\nJust for amusement, I've found that that in some places I have to convert dateTime to a string in Excel e.g. to  use dates as chart Axis labels.  But when you split it like your pivot does , you _can_ use 2017 / 2018 and month names in a clustered column chart. \r\n"}}
{"actor":{"display_login":"jhoneill"}}