topic Unable to convert date into monty year format in Community Discussions

Unable to convert date into monty year format

srk80 — Thu, 31 Jul 2025 17:43:56 GMT

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?

Re: Unable to convert date into monty year format

ZF — Mon, 04 Aug 2025 22:02:07 GMT

Try this formua:

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

Re: Unable to convert date into monty year format

Buttler — Sun, 15 Mar 2026 20:57:29 GMT

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.

Re: Unable to convert date into monty year format

mischa869 — Wed, 01 Apr 2026 02:01:45 GMT

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.