nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Convert date format into a new column

Valerie_Nedbal

Hello,

I want to derive a date dd.mm.yyyy (character format) into a new column and into yyyy.mm.dd date format. I'm getting a hard to do this. i have attached an example of the input column.

Thanks for your help,

Valeríe

Example.jmp

Accepted answers

ian_jmp

Perhaps there's a better way, but this should work:

New Table( "Example.jmp",
	Add Rows( 15 ),
	New Column( "MonthYear",
		Character,
		"Nominal",
		Set Values(
			{"01.01.2013", "01.02.2013", "01.01.2013", "01.01.2013", "01.01.2013",
			"01.02.2013", "01.04.2013", "01.01.2013", "01.02.2013", "01.02.2013",
			"01.02.2013", "01.04.2013", "01.07.2013", "01.11.2014", "01.12.2014"}
		)
	),
	New Column( "JMP Date",
		Numeric,
		"Nominal",
		Format( "yyyymmdd", 9 ),
		Input Format( "yyyymmdd" ),
		Formula(
			Date DMY(
				Num( Word( 1, :MonthYear, "." ) ),
				Num( Word( 2, :MonthYear, "." ) ),
				Num( Word( 3, :MonthYear, "." ) )
			)
		)
	)
)

All comments

ian_jmp

Perhaps there's a better way, but this should work:

New Table( "Example.jmp",
	Add Rows( 15 ),
	New Column( "MonthYear",
		Character,
		"Nominal",
		Set Values(
			{"01.01.2013", "01.02.2013", "01.01.2013", "01.01.2013", "01.01.2013",
			"01.02.2013", "01.04.2013", "01.01.2013", "01.02.2013", "01.02.2013",
			"01.02.2013", "01.04.2013", "01.07.2013", "01.11.2014", "01.12.2014"}
		)
	),
	New Column( "JMP Date",
		Numeric,
		"Nominal",
		Format( "yyyymmdd", 9 ),
		Input Format( "yyyymmdd" ),
		Formula(
			Date DMY(
				Num( Word( 1, :MonthYear, "." ) ),
				Num( Word( 2, :MonthYear, "." ) ),
				Num( Word( 3, :MonthYear, "." ) )
			)
		)
	)
)

martindemel

Isn’t Brady brady’s Data tool addin the perfect way dousing this? You can specify what are the separators what each number is as a role and specify the output firmat

brady_brady

Valerie,

This will do it:

dt = Current Data Table();
dt << new column("NewDate", formula(Informat( Char( Substitute( :MonthYear, ".", "/" ) ), "D/M/Y" )), format("Y/M/D"));

But, as Martin mentions, I posted an addin that writes code for you... all you have to do is make selections from a dialog. It handles this and many other, more antagonistic, examples.

Look for the "Data Table Tools" addin, and when running it, choose the "Custom Date Formula Writer" from the "Special Formula Columns" submenu.

The formula won't be as concise as the one above, but you won't have to come up with it yourself!

Cheers,

Brady