topic Re: How to convert character formats to date format in Discussions

How to convert character formats to date format

saitcopuroglu — Sat, 10 Oct 2015 08:27:39 GMT

Dear All,

I receive data in excel with Date columns in various format (defined as character)

31.02.2015

31.2.2015

Feb 31 2015

and many more..

Trying in excel to format them but does not work for excel import wizard (mac)

How will I convert them to one valid Date Column in JMP?

Thank you in advance for your support.

Re: How to convert character formats to date format

saitcopuroglu — Mon, 12 Oct 2015 06:35:21 GMT

Any idea?

Re: How to convert character formats to date format

ron_horne — Mon, 12 Oct 2015 10:55:06 GMT

perhaps this can work for you:

https://www.youtube.com/watch?v=9nRaDwsYB28

ron

Re: How to convert character formats to date format

Steven_Moore — Mon, 12 Oct 2015 11:01:01 GMT

I have also struggled with date formats importing to JMP. What I do is this: In Excel, I format my date column as "short date", i.e, 6/23/15. Then, after importing the spreadsheet into a JMP data sheet, I then double click at the top of the date column and change the data type to Numeric and then change the modeling type to "Continuous". Next click "Input Format" and click "Date" in the drop down menu. You can then select the date format you wish to see in your JMP data table.

Re: How to convert character formats to date format

MikeD_Anderson — Mon, 12 Oct 2015 14:36:53 GMT

Try this out (see the formula column in the attached data table)...You can expand for the different date formats you encounter.

Best,

Re: How to convert character formats to date format

MikeD_Anderson — Mon, 12 Oct 2015 18:33:06 GMT

Looking at my example and ron_horne's example... they are basically the same concept... Use the informal() function.

The part that I didn't know, that the video indicates, is that if you leave out the second option of the informat() function, usually informal(<striing column>, <string format>), it will actually do that same thing as what I was proposing, but with a lot less coding.

So, make a function in your data table that contains "Informat(<date column>)" and it should have you set.

Please be sure to mark ron_horne's answer as the correct one if that helps you out as it is the more elegant of the two solutions.

Best,

Re: How to convert character formats to date format

saitcopuroglu — Tue, 13 Oct 2015 08:31:21 GMT

Thank you for all the input!

After trying several weird character date formats imported as character through excel import wizard (mac), the informat function with deleting the secondary argument can handle most of the process (12 of 14 different char formats).

Once again many thanks to JMP community!

Re: How to convert character formats to date format

saitcopuroglu — Wed, 19 Oct 2016 00:56:38 GMT

One of the two char formats which informat function is unable to resolve is as screenshotted.

The function can resolve only the month of "May", I guess because it is the only 3 char month as its own and equals to abbreviated month char.

Recoding all months except May or Finding and Replacing all months to 3 char abbr. ones would be a solution of course but none of them would be that "elegant".

Any idea would be appreciated:

Re: How to convert character formats to date format

MikeD_Anderson — Thu, 15 Oct 2015 13:58:27 GMT

Two options off the top of my head. Use the Recode Utility to change them all at once. Or you could use the nested Informat() arguments I proposed. You could build in a Match() argument into the logic to translate the long form month names into something JMP expects.

Best,

Sent from my iPhone

Re: How to convert character formats to date format

glenn_maxey0 — Thu, 15 Oct 2015 16:18:17 GMT

I'm talking out of turn here. If this is a one-time conversion, then sometimes it is easier to write VBA macros to get the data in the source to conform to some standard before exporting and then importing into JMP. YMMV.

Re: How to convert character formats to date format

MikeD_Anderson — Thu, 15 Oct 2015 20:36:38 GMT

No, not out of turn at all... My VBA is just really weak ... sticking to my strengths here!

Re: How to convert character formats to date format

SDF1 — Thu, 28 Mar 2019 17:41:36 GMT

Hi @MikeD_Anderson,

I was having a similar issue as the original post did, but more with converting a date format from German style: dd.mm.yyyy to mm/dd/yyyy.

I tried the InFormat() approach, but it didn't work, whereas the formula in your date example data table worked perfectly! So, I would also mark yours as a correct solution, even though it's not as elegant.

Thanks!,