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
Valley_Gurl
Level II

Combining separate date and time columns to make ISO 8601 format (yyyy-mm-ddThh:mm)

I am trying to generate a ISO 8601 formatted column, which combines two sepatate date and time columns into a joint date/time form (yyyy-mm-ddThh:mm). Here is an example:

 

DATE (yyyy-mm-dd)TIME (:day:hr:m:s)

DATE/TIME (yyyy-mm-ddThh:mm)

2000-07-20:0:09:30:002000-07-20T09:30
1993-04-30:0:12:01:001993-04-30T12:01
2003-05-05:0:16:28:002003-05-05T16:28

 

I have tried to simply change the format for the "TIME" column to yyyy-mm-ddThh:mm but that gave me dates in 1904. Any thoughts?

1 ACCEPTED SOLUTION

Accepted Solutions
cwillden
Super User (Alumni)

Re: Combining separate date and time columns to make ISO 8601 format (yyyy-mm-ddThh:mm)

Hey @Valley_Gurl,

 

Luckily, this is easier than it looks.  12:00:00 AM January 1, 1904 is the baseline value for date/time values in JMP.  If you have date only values (first column), underlying the presented formatted value is a numeric value that is the number of seconds elapsed the baseline time in 1904 to midnight of the given date value.  If you have a time only value (your second column), it is the number of elapsed seconds since midnight (on January 1, 1904 if you convert it to date/time).  Therefore, all you need to do is sum them together, and then apply a format.

 

The column formula for data/time:

 formula.PNG

 

Applying the format:columninfo.PNG

*Make sure the input format is "Numeric" since the result of the sum will just be a large integer representing the number of seconds from baseline to the desired date/time value.  

Result:

 

 

Result.PNG

 

 

-- Cameron Willden

View solution in original post

1 REPLY 1
cwillden
Super User (Alumni)

Re: Combining separate date and time columns to make ISO 8601 format (yyyy-mm-ddThh:mm)

Hey @Valley_Gurl,

 

Luckily, this is easier than it looks.  12:00:00 AM January 1, 1904 is the baseline value for date/time values in JMP.  If you have date only values (first column), underlying the presented formatted value is a numeric value that is the number of seconds elapsed the baseline time in 1904 to midnight of the given date value.  If you have a time only value (your second column), it is the number of elapsed seconds since midnight (on January 1, 1904 if you convert it to date/time).  Therefore, all you need to do is sum them together, and then apply a format.

 

The column formula for data/time:

 formula.PNG

 

Applying the format:columninfo.PNG

*Make sure the input format is "Numeric" since the result of the sum will just be a large integer representing the number of seconds from baseline to the desired date/time value.  

Result:

 

 

Result.PNG

 

 

-- Cameron Willden