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