Combining Date Column and Time Column to Create Timestamp

[#1]

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?

[#2 · Accepted solution]

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, 

txnelson_0-1645062309651.png

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

txnelson_1-1645062620851.png

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

txnelson_2-1645062823276.png

[#3]

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?

[#4]

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