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

Valley_Gurl

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:00	2000-07-20T09:30
1993-04-30	:0:12:01:00	1993-04-30T12:01
2003-05-05	:0:16:28:00	2003-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?

cwillden

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:

 

 

Applying the format:

*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: