nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Combining Date Column and Time Column to Create Timestamp

user8421

For a data table with a date column and a time column, I create a new column that uses the formula Date + Time to create a timestamp. However the formula seems to work inconsistently where some of the timestamps result in being 4 YEARS previous than what they should be.

Date	Time	Timestamp (formula)
2021 - 10 - 01	12:05 AM	10/01/2021 12:05 AM
2021 - 10 - 23	6:59 PM	10/23/2017 6:59 PM
2021 - 11 - 07	7:45 PM	11/07/2017 7:45 PM

Is there a better way to generate a timestamp that avoids whatever causes this issue?

Accepted answers

txnelson

Assuming that your Date column and your Time column are JMP date/time values, my suggestion is that you need to change the display format for your 2 columns to display both date and time to see if the Time column contains date values In this example, Column 6 and Column 7 display the same time values,

however, when one examines the actual numeric values, they are completely different.

The Column 6 actually contains the date component , so what I am guessing is that in your time value, there are additional date related values

All comments

txnelson

however, when one examines the actual numeric values, they are completely different.

The Column 6 actually contains the date component , so what I am guessing is that in your time value, there are additional date related values

user8421

Thanks for the explanation. When I changed the format of the Time column it showed different dates with one being the 01/01/1994 and the other 12/31/1899. The raw data that is pulled into JMP lists the time only, so I am unclear how the date is "assigned" to the times. How can I address the discrepancy, so that the dates are consistent in the time columns?

txnelson

I would run the 2 scripts below to make sure the Date values only point to the start of the day you are specify. Just up to the start of the day 00:00:00.

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:date = Date MDY(
		Month( :date ),
		Day( :date ),
		Year( :date )
	)
);

And with a slightly different approach, fix the :Time column

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:time = Hour( :time ) * 3600 
	+ Minute( :time ) * 60
	+ Second( :time )
);

These 2 scripts will insure that Date has no times in the data, and the second one has no dates in :Time