macfan55 Posted August 15, 2018 Share Posted August 15, 2018 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? Quote Link to comment Share on other sites More sharing options...
step Posted August 15, 2018 Share Posted August 15, 2018 return Field("Date").replace(/\//g, '-'); Quote Link to comment Share on other sites More sharing options...
macfan55 Posted August 16, 2018 Author Share Posted August 16, 2018 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. Quote Link to comment Share on other sites More sharing options...
ScottHillock Posted August 16, 2018 Share Posted August 16, 2018 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(); Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted August 16, 2018 Share Posted August 16, 2018 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"); Quote Link to comment Share on other sites More sharing options...
macfan55 Posted August 17, 2018 Author Share Posted August 17, 2018 Thanks for the answers! This has been again a great help for me! Greetings from the Netherlands 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.