topic Re: Create m/y column from separate character month and year columns in Discussions

Create m/y column from separate character month and year columns

Dukowitz62 — Sat, 10 Jun 2023 23:35:46 GMT

How do I create a date column with format yyyymm or m/y from two character columns entitled Month and Year? This data is imported from city water billing system as a text file. See attached example. I know how to concatenate the columns into another character column, but we will need the output column formatted as a date column to perform frequency calculations.

Re: Create m/y column from separate character month and year columns

Dan_Obermiller — Tue, 17 Aug 2021 23:29:24 GMT

JMP stores dates as the number of seconds since January 1, 1904. As such, you must supply a day, you just don't need to display it or use it.

I noticed that you had your month and year columns listed as character strings. They probably should be numeric, but this formula will work with them as character string:

Notice that the Date MDY function requires the numeric Month, numeric Day, and numeric Year values to be entered. I used the first day of each month as the default. Then use the column info on this new column to set the format to be numeric, and the format of m/y. I have attached the new table with the formula in it.

Re: Create m/y column from separate character month and year columns

Dukowitz62 — Wed, 18 Aug 2021 00:28:56 GMT

That makes perfect sense. Thank you for your response.