cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Herrera5238
Level III

How to replace "." or "-" character to "/"

Hello,

I started a script that imports multiple excel files into one table. The date field has a mix of formats; mm/dd/yyyy , mm.dd.yy and mm-dd-yy.

I tried using recode, and looking at the script it looks like static values:

Current Data Table() << Begin Data Update;
dc1 = New Column( :Date );
dc1 << Set Name( "Date 3" );
Current Data Table() << Go to( dc1 );
Current Data Table() << Move Selected Columns( after( :Date ) );
For Each Row(
dc1[] = Match( :Date,
"10.11.18", "10/11/18",
"10.16.18", "10/16/18",
"10.20.18", "10/20/18",
"10.21.18", "10/21/18",
"10.24.18", "10/24/18",
"11-01-18 A1", "11/01/18 A1",
"11.08.18", "11/08/18",
:Date
)
);
Current Data Table() << End Data Update;

Essentially what I'm looking for as my end result is having all the dates formatted in a short date format, mm/dd/yy.

I need help making this code work for any new values that may be added later or maybe there is a better way to do it?

 

1 REPLY 1

Re: How to replace "." or "-" character to "/"

This is a common issue for users. A search of previous discussions will find a few solutions that might suit you. Here is one discussion from which you can adapt an answer.