extracting month and year from mm/dd/yyyy date format

[#1]

Hi,

I need to extract the month and year information from Date column in format of mm/dd/yyyy to mm/yyyy so I can find the mean of turnaround time by month. Month(:Date) extract month and Year(:Date) give year of the date. But I need it both in same formula (or same column). Tried Month(:Date) || Year(:Date) worked but resulting date type - 'expression' won't allow to plot the data.

The attached table "Turnaround Time" consist of Start Dates of analysis and turnaround time (or Net workdays needed to complete the analysis).  I need to find the mean turnaround time for each month and plot it in 'Fit Y by X' format where X will month of each year and Y will be mean turnaround time for each month.

Man thanks in advance for your support!

[#2 · Accepted solution]

Hi Rahul,

Have you tried using the the New Formula Column option found when right clicking on the column header.  You can highlight both columns at once and then right click to use the New Formula Column > Date Time > Month Year.  The result should look like the attached JPG.

HTH,

Bill

[#3]

Thank you!

[#4]

I've posted this before, but in case anyone else sees this post:

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.