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.
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