Jump to content

Date from Excel won't format


JeremyT

Recommended Posts

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"?

Link to comment
Share on other sites

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");

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • 2 weeks later...
  • 6 months later...

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");

Link to comment
Share on other sites

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.

ScreenShot2016-02-29at2_11_44PM.jpg.9bf369959446c1e16ee6f864dd27d713.jpg

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 7 months later...

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...