BookmarkSubscribeRSS Feed

new column & create M/Y date

I'd like to create a new date column based on data in other columns. I have 2 columns containing dates in m/d/y format. I have been able to parse out the month and year for each to create 4 new vars.

Now, I'd like to create a new var that is based on the month and year of one date, and then I'll backfill the second date if the first is missing, but I want the new var to be in M/Y format.

I am not seeing any script guidance as to how to pull the month and year back together to create the new M/Y var. Any assistance is much appreciated!
6 REPLIES

Re: new column & create M/Y date

dang it, i just figured it out. sorry for posting my query too hastily.

Re: new column & create M/Y date

hello,

could you shortly explain? Have the same problem. Very pedestrian going back to excel, doing the grouping there and then for analysis back to JMP.

Thank you.

Re: new column & create M/Y date

This is the script I ended up using. There is probably a more elegant way of doing the same thing, but this works well enough for my purposes:

New Column( "start.month", Numeric, Continuous, Format( "Best", 10 ), Formula( Month( :start.date ) ) );
New Column( "start.year", Numeric, Continuous, Format( "Best", 10 ), Formula( Year( :start.date ) ) );

New Column("start.date.rev", format("m/y"),formula(Date MDY( :start.month, 1, :start.year)));

for each row(
If(IsMissing(admin.date),admin.date=start.date.rev);
If(IsMissing(admin.year),admin.year=start.year);
);
rossmiller

Community Trekker

Joined:

Jun 23, 2011

Re: new column & create M/Y date

i've been trying to do exactly the same thing, change a full date into m/y format. is there a way to do this without running a script using a formula?

i created the columns using the month and year functions, but i can't concat them together
ms

Super User

Joined:

Jun 23, 2011

Re: new column & create M/Y date

This formula seem to work,

Informat( Char( Month( :Date ) ) || "-" || Char( Year( :Date ) ), "m-y" )

Remember to set the column format to numeric, continuous, and date: m-y.
GoodMan

Community Trekker

Joined:

Oct 28, 2011

new column & create M/Y date

MS, thanks for your answer to this data question. If i need output format like"y-m", not "m-y", how can i realize with formula? Here in my country, we just accept "y-m" or "y-m-d". Thank you.