JeremyT Posted August 3, 2015 Share Posted August 3, 2015 I recently upgraded Fusion Pro VDP to version 9.3.15. One of the new features is being able to use native Excel Spreadsheet files as a data source. This saves a step since I don't have to save it to a delimited CSV or TXT file. I've run into an issue with formatting a date from a native Excel file. Previously the date from tab delimited TXT file came into FusionPro as "07/26/2015". Now from native Excel Spreadsheet it comes in as "42218" for date 8/2/2015, "42211" for 07/26/2015, "42197" for 07/12/15, "42183" for 06/28/15. Date field has "Date" formatting applied in Excel. I format the Date field with this rule. var DateShort = FormatDate(Field("Date"), "m-dd-yy")Is there an adjustment to this rule so that I can use the Excel Spreadsheet and return date as "m-dd-yy"? Quote Link to comment Share on other sites More sharing options...
step Posted August 3, 2015 Share Posted August 3, 2015 I've never done what you're trying to do but it sounds like FusionPro is being passed Excel's internal date rather than the formatted text. Have you tried converting the date cells to actual text rather than dates? On the other hand, it appears to me from your examples that Excel is calculating dates based on how many days after December 30, 1899 something is: July 26, 2015 is 42,211 days after December 30 1899. So you could use this function to format it in FusionPro if you want: function ExcelDate(input,format){ if (!input) return ''; var date = new Date('12/30/1899'); date.setDate(date.getDate() + Int(input)); return FormatDate(date,format); } return ExcelDate(Field("Date"),"m-dd-yy"); Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted August 3, 2015 Share Posted August 3, 2015 What is the format of the cell in Excel? In other words, if you select the cell in Excel, right-click, and select "Format Cells", then on the the "Number" tab, what does it say in the "Category" list? And if that's set to "Date", what does it say in the "Type" list? In a test spreadsheet here, if the Category is set to "Date" and the Type is set to "*3/14/2001" (the first one in the list), then FusionPro reads the date in that format. If you have the Category or Type set to something else, though, it may not be interpreted correctly. Can you attach your Excel file, or at least enough of it to demonstrate the issue? Quote Link to comment Share on other sites More sharing options...
jimmyhartington Posted August 12, 2015 Share Posted August 12, 2015 I recently upgraded Fusion Pro VDP to version 9.3.15. I wonder hos you got 9.3.15? As I can see on http://marcom.com/fusionpro-downloads/ the latetst versions is v9.3.6 on Mac and v9.3.9 on Windows. Quote Link to comment Share on other sites More sharing options...
dreimer Posted August 12, 2015 Share Posted August 12, 2015 Version supplied from FPI support. Quote Link to comment Share on other sites More sharing options...
JeremyT Posted February 29, 2016 Author Share Posted February 29, 2016 I added the code that Step suggested and got the date formatted correctly. Thanks! I've never done what you're trying to do but it sounds like FusionPro is being passed Excel's internal date rather than the formatted text. Have you tried converting the date cells to actual text rather than dates? On the other hand, it appears to me from your examples that Excel is calculating dates based on how many days after December 30, 1899 something is: July 26, 2015 is 42,211 days after December 30 1899. So you could use this function to format it in FusionPro if you want: function ExcelDate(input,format){ if (!input) return ''; var date = new Date('12/30/1899'); date.setDate(date.getDate() + Int(input)); return FormatDate(date,format); } return ExcelDate(Field("Date"),"m-dd-yy"); Quote Link to comment Share on other sites More sharing options...
JeremyT Posted February 29, 2016 Author Share Posted February 29, 2016 What is the format of the cell in Excel? In other words, if you select the cell in Excel, right-click, and select "Format Cells", then on the the "Number" tab, what does it say in the "Category" list? And if that's set to "Date", what does it say in the "Type" list? In a test spreadsheet here, if the Category is set to "Date" and the Type is set to "*3/14/2001" (the first one in the list), then FusionPro reads the date in that format. If you have the Category or Type set to something else, though, it may not be interpreted correctly. Can you attach your Excel file, or at least enough of it to demonstrate the issue? Dan, Format Category of the cell is "Custom". Type is "[$-10409]mm/dd/yyy" I've attached a screen shot. If I change the format in Excel to "Date", type "3/14/01", it shows up as 42424 in FusionPro for 02/24/16. Thanks. Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted March 1, 2016 Share Posted March 1, 2016 Format Category of the cell is "Custom". Type is "[$-10409]mm/dd/yyy" Okay, thanks for the clarification. We did recently add support for more of the date and time formats in Excel, including the specific one you're using. This should be available in an upcoming release. Quote Link to comment Share on other sites More sharing options...
Printing Partners Posted October 12, 2016 Share Posted October 12, 2016 I have had this problem as well. Save the Excel file as an XLSX file, then try it again. I have found that an Excel file back saved in an older XLS format causes this error. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.