BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar

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 6

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.
Highlighted

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.