JMP date convert not working?

Question

I hope someone can help with this.  I imported an excel file with a column of dates in the format yyyymmdd.  It didn't recognize this data as time, so I tried to change it by selecting column info > Format > Date>yyyymmdd.

it then converts all of my dates to some other number  but it seems that the number is the same for all my dates. Obv. this is a pretty standard task so I guess I am doing something wrong. I have attached a screenshot of my data the left column is the original, the right one is where I took that column and converted it the way that I described.

version  JMP® Pro 18.0.2

Thanks for the help. Any other advice on getting JMP to recognize time correctly would be really valuable.

timedata.jmp

christian-z · Answer

The reason for this is because a date of the form "20191107" is not actually a number, it is a character string. For example, consider which date would come after "20191130"; the next string should be "20191201", not "20191131". This is not the behavior of a number, but rather of a numerical representation of a Date object. Here's some more info on this topic: Using dates, times, datetimes and durations in JMP. (JMP's underlying encoding of dates and times in is seconds, hence the strange looking conversion you noted above).

JMP is usually good at detecting date strings and automatically converting them from Character to Numeric type (which can then be set to display as a date in the preferred Format). For example, it would be obvious to us (and to JMP) that a string of the form "2019-11-07" is a Character string and not a Number. However in this case, dates of the form yyyymmdd look just like numbers, so the extra step of converting to Character is needed, otherwise JMP will treat it as a regular number.

If you need to perform this conversion often, one option would be to create a Formula column with the following:

Format(Parse Date( Char( :Column 1 ), "yyyymmdd" ),"yyyy-mm-dd")

jthi · Answer

Formatting is used to change how the number is being displayed not the value is my guess -> converting to character allows you to basically bypass this and allows JMP to "recalculate" the string into a number.

TMS · Answer

This helped, thanks.

first I had to convert the column to character. then I immediately change it back to numeric and when I do so I also select the format as date and yyyymmdd and hit apply and it works - or at least it doesn't overwrite the dates with some other number.

I would love to hear from JMP why it is necessary to change it to character first.  It's not obvious to a new user why this would be needed. Seems like an unneccessary step and should be in the standard instructions somewhere.