topic Change Date format in Discussions

Change Date format

DecileIbex201 — Wed, 31 Jul 2024 03:31:11 GMT

There is an error as I cannot run this, can anyone help?

// Function to reformat dates to dd/mm/yyyy
colName = Column Dialog( "Select Date Column" );
reformatDateColumn = Function({dt, colName},
dt = Current DataTable();If( Column( dt, colName ) == Empty(),
Throw( "The selected column does not exist in the current data table." );
);

// Function to identify and reformat the date
formatDate = Function({date},
// Check for different date formats and convert to dd/mm/yyyy
If( Contains( date, "/" ),
// Check if date is in dd/mm/yyyy or mm/dd/yyyy format
If( Substr( date, 3, 1 ) == "/",
// Assume dd/mm/yyyy format
date,
// Assume mm/dd/yyyy format and convert to dd/mm/yyyy
Concat( Word( 2, date, "/" ), "/", Word( 1, date, "/" ), "/", Word( 3, date, "/" ) )
),
// Handle mmddyyyy, ddmmyyyy, yyyymmdd, or yyyyddmm format
If( Length( date ) == 8,
// Check if first two digits form a valid month or day
If( Num( Substr( date, 1, 2 ) ) > 12,
// Assume ddmmyyyy format
Concat( Substr( date, 1, 2 ), "/", Substr( date, 3, 2 ), "/", Substr( date, 5, 4 ) ),
// Assume mmddyyyy format and convert to dd/mm/yyyy
Concat( Substr( date, 3, 2 ), "/", Substr( date, 1, 2 ), "/", Substr( date, 5, 4 ) )
),
If( Length( date ) == 8,
If( Num( Substr( date, 1, 4 ) ) > 999, // Check if first 4 digits form a valid year
// Check if the next two digits form a valid month or day
If( Num( Substr( date, 5, 2 ) ) > 12,
// Assume yyyyddmm format
Concat( Substr( date, 7, 2 ), "/", Substr( date, 5, 2 ), "/", Substr( date, 1, 4 ) ),
// Assume yyyymmdd format and convert to dd/mm/yyyy
Concat( Substr( date, 7, 2 ), "/", Substr( date, 5, 2 ), "/", Substr( date, 1, 4 ) )
),
// If not a valid year or format, return the original date
date
),
// If the date length is not 8, return the original date (invalid format)
date
)
)
)
);

//Create the UI with buttons to trigger the functions
New Window( "Data Processing Tools",
V List Box(
// Button to reformat date column
Button Box( "Reformat Date Column",
// Open a dialog to select the date column
colName = Column Dialog( "Select Date Column" );

// Call the function to reformat the selected date column
reformatDateColumn( Current Data Table(), colName );
)
)
);

Re: Change Date format

jthi — Wed, 31 Jul 2024 04:39:14 GMT

Could you explain what you are trying to instead of just the code (code looks like LLM created)?

Re: Change Date format

jthi — Wed, 31 Jul 2024 04:51:40 GMT

I assume you have a data table with a character column which looks like dates. And it can have mixed formats of dates? And you wish to have UI to convert those to numeric format? JMP's Format Pattern might be able to do this, but it would be helpful to have an example of the data.

Here is the original script in a bit easier to read format (hopefully I didn't make any changes outside of formatting the script)

colName = Column Dialog("Select Date Column");

reformatDateColumn = Function({dt, colName},
	dt = Current Data Table();
	If(Column(dt, colName) == Empty(),
		Throw("The selected column does not exist in the current data table.")
	);
);

formatDate = Function({date},
	If(Contains(date, "/"),
		If(Substr(date, 3, 1) == "/",
			date, 
			// Assume mm/dd/yyyy format and convert to dd/mm/yyyy
			Concat(Word(2, date, "/"), "/", Word(1, date, "/"), "/", Word(3, date, "/"))
		)
	, 
		// Handle mmddyyyy, ddmmyyyy, yyyymmdd, or yyyyddmm format
		If(Length(date) == 8, 
			// Check if first two digits form a valid month or day
			If(Num(Substr(date, 1, 2)) > 12, 
				// Assume ddmmyyyy format
				Concat(Substr(date, 1, 2), "/", Substr(date, 3, 2), "/", Substr(date, 5, 4))
			, 
				// Assume mmddyyyy format and convert to dd/mm/yyyy
				Concat(Substr(date, 3, 2), "/", Substr(date, 1, 2), "/", Substr(date, 5, 4))
			)
		,
			If(Length(date) == 8,
				// Check if first 4 digits form a valid year
				If(Num(Substr(date, 1, 4)) > 999, 
					// Check if the next two digits form a valid month or day
					If(Num(Substr(date, 5, 2)) > 12, 
						// Assume yyyyddmm format
						Concat(Substr(date, 7, 2), "/", Substr(date, 5, 2), "/", Substr(date, 1, 4))
					, 
						// Assume yyyymmdd format and convert to dd/mm/yyyy
						Concat(Substr(date, 7, 2), "/", Substr(date, 5, 2), "/", Substr(date, 1, 4))
					)
				, 
					// If not a valid year or format, return the original date
					date
				)
			, 
				// If the date length is not 8, return the original date (invalid format)
				date
			)
		)
	)
);

New Window("Data Processing Tools",
	V List Box(
		Button Box("Reformat Date Column", 
			// Open a dialog to select the date column
			colName = Column Dialog("Select Date Column");
			reformatDateColumn(Current Data Table(), colName);
		)
	)
);