Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 304 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Format Time value in NAV 2013 on Excel-import
Desdemona667
Member Posts: 8
Hello,
I'm having some trouble importing data from an excel-sheet via (custom) Report using the Excel Buffer table in NAV 2013.
Everything works as it should, except for time values. The value in the Excel Sheet is 13:30 (for example), but as soon as it is read into "Cell Value as Text" in the Excel Buffer, the value in NAV is 14:30. It makes no difference, whether the cells containing the time values in Excel are formatted as Standard or as Time.
I think this has something to do with the values from excel being formatted in NAV before they can be put in the Excel Buffer, but I'm not sure, whether this is general problem or just our’s. I have just tried changing my time zone to UTC (we’re UTC+1) and then the import ran correctly. Looks to me like NAV expects the imported time to be UTC and adjusts it for the actual time zone of the system. Can this be due to some error in config or settings?
As mentioned above: We only have this problem in 2013 (no matter if including RollOut3 or not), in 2009 it worked ok.
Does anyone have any experience with this? I'm looking forward to any input you can provide.
Thank you.
Cheers,
Marina
Edit:
I have also tried changing the timezone in CustomSettings to Server Time Zone (which is UTC+1) but this didn't help. The time imported is still the time given in the Excel Sheet + 1 hour.
I'm having some trouble importing data from an excel-sheet via (custom) Report using the Excel Buffer table in NAV 2013.
Everything works as it should, except for time values. The value in the Excel Sheet is 13:30 (for example), but as soon as it is read into "Cell Value as Text" in the Excel Buffer, the value in NAV is 14:30. It makes no difference, whether the cells containing the time values in Excel are formatted as Standard or as Time.
I think this has something to do with the values from excel being formatted in NAV before they can be put in the Excel Buffer, but I'm not sure, whether this is general problem or just our’s. I have just tried changing my time zone to UTC (we’re UTC+1) and then the import ran correctly. Looks to me like NAV expects the imported time to be UTC and adjusts it for the actual time zone of the system. Can this be due to some error in config or settings?
As mentioned above: We only have this problem in 2013 (no matter if including RollOut3 or not), in 2009 it worked ok.
Does anyone have any experience with this? I'm looking forward to any input you can provide.
Thank you.
Cheers,
Marina
Edit:
I have also tried changing the timezone in CustomSettings to Server Time Zone (which is UTC+1) but this didn't help. The time imported is still the time given in the Excel Sheet + 1 hour.
0
Comments
please check time on Service Tier.
Igor Pchelnikov
Yeah, you're right, I forgot to mention that I tested the change of CustomSettings on my own local Service, so all three tiers are on the same machine - which uses UTC+1.
Cheers,
-Marina