+ Reply to Thread
Results 1 to 26 of 26

PowerPivot in Excel 2010 date import problem

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Question PowerPivot in Excel 2010 date import problem

    Hi

    Can' find a solution to this date problem. Please help if you found one.

    The date column in the excel data file is formatted as dd.mm.yyyy but by the time it gets into the PowerPivot (as linked table) the format of the date change form some of the dates.
    In PowerPivot dates as 12.11.2013 (11-Nov) are changed to 11.12.2013 (11-Dec) but dates as 13.11.2014 remains 13.11.2014.

    Basically if the day in may excel table exceed 12 PowerPivot will get the date right, but if the day it's under 12 will replace the day in my table with month in powerpivot window and I have no idea why.

    Any help would be appreciated.
    Thanks

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    If you click Settings on the PowerPivot tab, what are the Language settings set to?
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    English (US) - all 3

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Well, since that would imply mm.dd.yyyy it would explain why 12.11.2014 would be interpreted as 11-Dec, not 12-Nov. Try switching to a language that uses DMY format or convert the data in place using the Text To Column wizard in Excel before importing.

  5. #5
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    Thank you romperstomper
    Switching languages didn't helped (I've used Italian, but no luck), Text To Column it's a nice workaround but very annoying since PowerPivot it's not able to keep the info as was set in excel cells in terms of format style

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Are they actually stored as dates, or as Text, in your Excel table?

  7. #7
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    They're stored as dates in my excel table (as dd.mm.yyyy)

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Does it make any difference if you simply change the cell format to use mm/dd/yyyy as a test?

  9. #9
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Unhappy Re: PowerPivot in Excel 2010 date import problem

    Already did it, makes no difference for powerpivot
    although the regional setting of the OS is Eng(US) Office language is Eng(US) PowerPivot is Eng(US)
    If the day in may excel table exceed 12 PowerPivot will get the date right, but if the day it's under 12 will replace the day with month if what he sees in the cell could make a valid date (although wrong date)

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Just to check: when you change the number format of the cells, the appearance did change accordingly?

  11. #11
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    You're asking if I change the cell set as date in excel sheet to text?
    If yes, then yes the date will change accordingly to a serial date numbers

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    I actually meant that if you change the format to use mm/dd/yyyy format (i.e. a US date format), the displayed value changed before you tried to reimport to PP. I can't replicate your problem, or understand how it comes about given what you describe, I'm afraid.

  13. #13
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    The displayed values change accordingly in the excel table before reimport to PP.
    This problem occurred after I updated the version of PP from v.10 to v.11.0.3129.0
    In v.10 I never had this problem and luckily I discovered this problem by mistake.
    I don't know if this was generated by the conversion that excel did when I opened the file created in the old version of PP, But I've tried a clean reimport of the data intro a new file and the problem persists.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    I only have 11.0.3.0000.0

  15. #15
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    If you're willing to investigate further more I've attached a sample
    Thank you
    Book_Date_Test.zip

    late edit:
    11.0.3.0000.0 I see too in my PP window but I've downloaded from here :
    http://www.microsoft.com/en-us/downl....aspx?id=29074

    and there its v.11.0.3129.0
    Last edited by dandinro; 11-19-2014 at 09:37 AM. Reason: version

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    OK - I opened that up and could see the issue you describe, but simply clicking Update All on the PowerPivot tab corrects it for me. FWIW, my language settings (with your workbook loaded) are:
    Language options: Match Excel language
    Current language: United States
    Model language: United States

  17. #17
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    Wow, really?

    PPrefresh.JPG

    As you can see I don't have that option available. This is getting strange

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    On the PowerPivot tab in the Excel window.

  19. #19
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    well, there's no change after Update all on the PowerPivot tab

    but importing this sample file into a new PP the date are shown correctly now
    I don't know why or how but this is odd, it works with external files but it doesn't when the info it's into a table on the same file

  20. #20
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Well, Powerpivot can be cranky.

  21. #21
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    Thank you for your time romperstomper.
    Let's hope this was a mess created by my PP upgrade

  22. #22
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    If you copy the table into a new workbook and create a new PP model there, does that work properly?

  23. #23
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    No,it doesn't
    I even tried to copy and paste as values into a new file, reformat the cell and reimport intro PP from the same file but I got the same mess.
    The only thing that worked was importing the data as external file intro PP

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Might be worth trying the 'Send Feedback' option in the PP window then. If I could replicate it, I could pass it on to Microsoft, but it works for me as I said.

  25. #25
    Registered User
    Join Date
    08-01-2013
    Location
    Romania
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: PowerPivot in Excel 2010 date import problem

    I'll try to 'Send Feedback'.
    I forgot to mention that I use winXP. And this might be a cause too
    Thank you

  26. #26
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: PowerPivot in Excel 2010 date import problem

    Ah. They'll probably just tell you they don't support you any more then!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Where / How to get PowerQuery and PowerPivot for Excel 2010?
    By Xceller in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-29-2014, 12:58 PM
  2. Import .SE into Excel PowerPivot
    By Magnus86 in forum Excel General
    Replies: 0
    Last Post: 05-10-2013, 11:12 AM
  3. Import a list from SharePoint 2010 into an Excel 2010 worksheet
    By nivoe in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2013, 05:41 AM
  4. API/Service to interact ith PowerPivot excel 2010
    By sourav.majumder in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-12-2012, 04:08 AM
  5. Powerpivot Excel 2010
    By Reykjavik in forum Excel General
    Replies: 0
    Last Post: 07-13-2011, 09:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1