Jump to content

Importing Excel file alters the structure of "Date" in the output


macfan55

Recommended Posts

I have a problem in using native excel files with text formatted as "Date"

When I open the file in Excel the column "Date" is filled like 27-04-2012 but after importing it as data source into FusionPro it becomes 27/04/2012 in the output

How can this be solved without altering the native excel file?

:)

Link to comment
Share on other sites

Thanks for answering so quickly.

Unfortunately I made a mistake in my first example.

Not only it alters the - in excel to / in FusionPro.

But it also switches the first and second number in the field.

For the english speaking world this is correct but in the Netherlands the sequence of a date-field is: DD/MM/YYYY

In excel the date is read correct like 20-09-1926 (DD/MM/YYYY)

"Under the Hood" in the excel file itself this is representated as 9760

This is the 9760-th day after 0-0-1900

After importing the excel file in FusionPro it reads like 9/20/1926 (MM/DD/YYYY)

With the given first solution (thanks for that) I can change that to 9-20-1926

but this has to be 20-9-1926

Has anyone any idea how to tackle this conversion problem without altering the excel file itself? In FusionPro I could not find any preference on how to interpret the numbering system of an excel date-field to the correct formatting of a country like the Netherlands.

Thanks in advance.

Link to comment
Share on other sites

var DateSplit = Field("Date").split("/");
return DateSplit[1] + "-" + DateSplit[0] + "-" + DateSplit[2];

 

Alternatively, you can use javascript's date functions which might be more flexible.

 

var d = new Date(Field("Date"));
return d.getDate() + "-" + d.getMonth() + "-" + d.getFullYear();

Link to comment
Share on other sites

var DateSplit = Field("Date").split("/");
return DateSplit[1] + "-" + DateSplit[0] + "-" + DateSplit[2];

 

Alternatively, you can use javascript's date functions which might be more flexible.

 

var d = new Date(Field("Date"));
return d.getDate() + "-" + d.getMonth() + "-" + d.getFullYear();

Or:

var d = DateFromString(Field("Date"));
return FormatDate(d, "d-m-yyyy");

Or, if you want the day and month to always be two digits (such as "04"):

var d = DateFromString(Field("Date"));
return FormatDate(d, "dd-mm-yyyy");

Link to comment
Share on other sites

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...