Unable to convert date into monty year format

[#1]

I have a excel sheet and I would like to tell JMP that it is monthly data. Dates in the format of dd-mm-yyyy. How hard I tried, it is taking dd-yyyy. Any suggestions? 

[#2]

Try this formua:

new column("MonthYear", Input Format( "m/y" ), Format( "m/y", 10 ),formula(Date DMY( 1, Month( :ds ), Year( :ds ) ))); 

[#3]

I had a similar issue while importing monthly data from Excel into JMP. Sometimes the problem is related to how the column format is recognized during import. You might try checking the column properties and setting the data type to Date with the correct format, or adjusting the import settings so JMP reads the full date (dd-mm-yyyy). That helped in my case when working with monthly datasets.

[#4]

The first problem with date conversion is that, in the background, JMP is "reading" the date as the number of seconds since January 1, 1904, 12:00:00 AM (and Excel "reads" it from January 1, 1900 which is almost 4B seconds which we'll reach on October 3, 2026 at 07:06:40)! That's why you will sometimes see values in the 3.9B range when converting dates.

Second, although you can change the format of the time you are looking at, JMP will still "read" the time in the original imported format. For example, you I have a date of 1/15/2020 and I want to group all January dates in a MM/YYYY format (for graphing or tabulating), simply changing the format to MM/YYYY doesn't work - JMP will still "read" it as it's original imported date.

That being said, the most successful I've been at converting dates so they can be used are:

- For MM/YYYY from Date, I use: Date DMY( 1, Month( :TRANSACTION_DATE ), Year( :TRANSACTION_DATE ) ) with the MM/YYYY format.

- For YYYYQq from Date, I use: Date DMY(1, Floor( (Month( :TRANSACTION_DATE ) - 1) / 3 ) * 3 + 1, Year( :TRANSACTION_DATE )) with the YYYYQq format (i.e. 2020Q1).

Both essentially take all of the dates in a particular month and put them on the first day of the month or quarter, respectively. Seems like a weird workaround, but it works for doing what you need for Tabulating, Time Series, Graph Builder, etc.