cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Neo
Neo
Level VI

How to subtract a fixed duration from Date Time column?

I have a data table with entryDate as one of the columns. It is Numeric & Continuous type and 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?

 

When it's too good to be true, it's neither
2 ACCEPTED SOLUTIONS

Accepted Solutions
jthi
Super User

Re: 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)

 

-Jarmo

View solution in original post

pmroz
Super User

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:

pmroz_0-1704854772212.png

 

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 )
	)
);

View solution in original post

5 REPLIES 5
jthi
Super User

Re: 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)

 

-Jarmo
Neo
Neo
Level VI

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 ?

When it's too good to be true, it's neither
Jeff_Perkinson
Community Manager Community Manager

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

-Jeff
Neo
Neo
Level VI

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?

When it's too good to be true, it's neither
pmroz
Super User

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:

pmroz_0-1704854772212.png

 

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 )
	)
);