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

Valley_Gurl — Wed, 15 Aug 2018 15:55:13 GMT

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?

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

cwillden — Wed, 15 Aug 2018 16:36:28 GMT

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:

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

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

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