topic Please help me to make a calculated column of date type. in Discussions

Please help me to make a calculated column of date type.

Ronaldo — Sat, 10 Jun 2023 23:52:55 GMT

column1 = year (ex, 2021, 2022)

column2 = month (ex, Oct, Apr, )

column4 = day (ex, 9, 10, 11)

(column 1,2,3 = string)

I want to make a new column in the form of a date by adding the calculation formula and merging the year, month,
What should I do?

Re: Please help me to make a calculated column of date type.

txnelson — Thu, 11 Aug 2022 00:30:55 GMT

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Date",
	Format( "m/d/y" ),
	formula(
		theList = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov",
		"Dec"};
		Date MDY( Contains( theList, :month ), Num( :day ), :year );
	)
);

If you are doing this interactively, just go to the Cols pull down menu and select New Columns

Change the name to Date, or whatever else you want it to be
Click on the down arrow for the format, and select Dage=>m/d/y
Click OK
Right click on the header for the new column and select Formula
Paste in the formula for the formula above
Click on OK

Re: Please help me to make a calculated column of date type.

Ronaldo — Thu, 11 Aug 2022 13:33:35 GMT

First, thank you very much for your answer
I'm studying JMP while looking at your many answers

I don't think I explained enough in this question, so I'll explain it again

I'd really appreciate it if you could help me again

I want to make it in the target form in the fourth column of the JMP picture below
If I use the concat function, I can make shapes
But the problem is that it becomes a character type
I want to be a date type in the form of date/m/d/y

Only then can we match the payment amount by date in the other data table

Re: Please help me to make a calculated column of date type.

txnelson — Thu, 11 Aug 2022 14:48:04 GMT

I understood what you were asking about. The script and instructions I posted would work except for one small issue. I assumed that your Year column was numeric. You had clearly pointed out that the Day column was character, and the Month column had to be character. But, you did not specify what the Year column was, so I assumed it was numeric. The code below will recreate the sample data table you displayed and then will create the new column with the m/d/y format used for the Target column

Names Default To Here( 1 );

// Recreate the Sample Data Table
New Table( "Sample",
	Add Rows( 4 ),
	New Column( "Year", Character, "Nominal", Set Values( {"2022", "2022", "2022", "2022"} ) ),
	New Column( "Month", Character, "Nominal", Set Values( {"Jan", "Jan", "Jan", "Jan"} ) ),
	New Column( "Day", Character, "Nominal", Set Values( {"1", "2", "3", "4"} ) )
);

// Create the Target column
dt = Current Data Table();
dt << New Column( "Target",
	Format( "m/d/y" ),
	formula(
		theList = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov",
		"Dec"};
		Date MDY( Contains( theList, :month ), Num( :day ), Num( :year ) );
	)
);