Need to format/convert date coming from Excel spreadsheet.

[pbare]

Hi all,

Hopefully someone can help me with this issue. My vendor is sending us an Excel spreadsheet that we import data from. The issue is that the date fields that they are sending us are in the format DD-MMM-YY (ex: 09-Apr-08)

I am trying to find a BFSN that will convert this to the format used in E1 (DD/MM/YYYY - eg: 09/04/2008)

At the moment, I am trying to convert the date to a string, format it correctly and then convert the string back to a date.

Is there an easier way to do this? Also what business functions are better for doing this - I found several in the program and I am not sure which one works best.

I am using Windows XP with E1 8.0 with SP23

thanks,
pbare

 

[GSI Inc.]

Without re-researching the BSFNs myself, what I have done in the past is convert it, substr it and put it back together with the / or whatever format is needed. I created my own NER BSFN to do this at a previous client. I know it is not perfect but I know of another developer recently asking the same question and that is all she could come up with in a pinch ~Angelis~

 

[dschlieder]

Not sure if I am not understanding this but in Excel you simply highlight the column in excel and you can change the date format to any standard format - if you do this before you import into JDE (I'm assuming through a grid)I don't think you should have any problem - but maybe I'm missing something here.

 

[pbare]

You aren't missing anything - I have suggested this to my business owners as a solution but they would rather the work be done by some other process, rather than them remembering the change the formats every time a file comes in.

Any thoughts on the easiest way to do this within E1 would be appreciated.

pbare

 

[JMR]

There is a date api, DeformatDate, that may be able to convert your date. It looks like your date format: DD-MMM-YY can be represented with the format string of: "A-B-R".

While I don't specifically know of any existing bsfn's that call the forementioned api and also allow you to pass the formatting string, I vaguely recall others stating they exist. What this api would return is a jde date which you could then pass to the FormatDate api to format it as you wish. Also look for an existing bsfn that calls FormatDate with a variable format string.

Creating your own bsfn to wrap these two calls would not take too much effort. It would look something like:

DeformatDate( &lpDS->jdDateOut, lpDS->szStringDateIn, lpDS->szInFormatString );
FormatDate( lpDS->szStringDateOut, &lpDS->jdDateOut, lpDS->szOutFormatString );

That's all the code. Your data structure would look something like:
jdDateOut [based on any date variable]
szStringDateIn [based on a string variable that is large enough to hold any input date]
szInFormatString [string variable...this will take your "A-B-R"]
szOutFormatString [string variable...this will look something like: "ASOSE"]
szStringDateOut [based on any string variable that is large enough to hold the output string date]

I encourage you to look at the api documentation for the date api's to understand the meaning of the format strings. I have made the assumption that your input format can be represented by the available formatting options...that may not be true.

Good luck.

 

[peterbruce]

Pam,

Have a look at the business function: Convert String to Date Using Format Mask