How to subtract a fixed duration from Date Time column?I have a data table with entryDate as one of the columns. It has row entries in the following format
07/07/2023 08:20:21
I want to subtract the following an offset from it and get newEntryDate in a new column.
184 days, 14 hours, 14 minutes and 43 seconds
(or 6 months, 2 days, 14 hours, 14 minutes, 43 seconds)
How to do this in JMP/JSL?
Also, any newEntryDate which results in a negative value after the above operation needs to be replaced by the corresponding row entry in the entryDate column. This I can do part manually, but a formula/JSL option is preferable.
Automation and ScriptingWindowsRe: How to subtract a fixed duration from Date Time column?Convert your time to seconds and then you can deduct it
:entryDate - (In Days(184) + In hours(14) + In Minutes(14) + 43)
Re: How to subtract a fixed duration from Date Time column?@jthi Thanks. I almost managed it but did not realize that 43 could be subtracted directly I was looking for a In Seconds () function.
While I am on this topic, how do I get the difference/duration in seconds between :entryDate and :newEntryDate ?
Re: How to subtract a fixed duration from Date Time column?Dates, times, and datetimes are stored as a number of seconds. So to get the difference between two dates in seconds just subtract one from the other.
If you want the difference in other units you can use the Date Difference() function.
Read more: Using dates, times, datetimes and durations in JMP.
Re: How to subtract a fixed duration from Date Time column?@Jeff_Perkinson . OK thanks. How do I convert the date in the format I have in seconds?
Re: How to subtract a fixed duration from Date Time column?>>It is Numeric & Continuous type
If your date column is numeric/continuous as you have specified, then it is already in seconds, behind the scenes as it were. You must have specified a column format of m/d/y h:m:s. If you want to display seconds, then use a numerical format.
Here's a table where the first column uses the date format, and the second one uses numeric format:
New Table( "Test Date", Add Rows( 2 ),
New Column( "Date Format", Numeric, "Continuous",
Format( "m/d/y h:m:s", 23, 0 ),
Input Format( "m/d/y h:m:s", 0 ),
Set Values( [3771562821, 3771562821] )
),
New Column( "Date in Seconds", Numeric, "Continuous", Format( "Best", 12 ),
Formula( :Date Format )
)
);
