cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar

Function to return date

Hi,

 

Often I get data with full time date stamps, really just the seconds since jan 1 1904.  What I would like is a simple way to turn this into a date.  Lump everything from midnight through 11:59PM.

 

Yes I can write a formula to do this but it would easier if

A. there was a Time format that would truncate  this 01/26/2018 10:47 PM  and 01/26/2018 4:13 AM to 01/26/2018 and model it as such.  I can display it as a date, but it is modeled so they would be the same value.

 

Failing that

 

B. An option from the equation builder to return the date.  Yes I know I can write with a formula, but I think more than myself is doing this.

 

Andy

5 Comments
Jeff_Perkinson
Community Manager

As you've discovered, JMP stores both datetime and date values as the number of seconds since midnight on January 4, 1904.

 

The only difference is that date values don't have a time of day component. That is they count until midnight on date.

 

It's easy to convert from datetime to date just use the the Time Of Day() function to get the time portion of the datetime value and subtract it off.

 

Date = datetime - Time Of Day(datetime);
julian
Community Manager

Hi Andy,

 

One way I like to do this is by using JMP's instant transforms: right-click a column in the table, select New Formula Column > Date Time > Date. You can even do this in a launch window if you want to make a temporary variable.

 Date.gif

 TempVar.gif

  

 

Jeff_Perkinson
Community Manager

I’m old-school, @julian is new-school. Nice. 

Hegedus
Level IV

Thank you for the tip about using the new formula column.

 

Let me expand the concept a bit more with the background that I want to avoid creating new columns unless necessary.  I like to keep my data tables compact.

 

How about expanding the idea that data handling follows data format.  If I have timestamp data and I format the column to day/month/year, JMP could automagically ignore the time portion of the data when handling it.

 

The use can be expanded.  I am old school and learned about significant digits in science classes, unfortunately the computer science majors were absent that day and as a result the constanly spit out data files with 8 or 10 digits of precision because well we can.  Again only the first three are really useful.  I can create a column and do the truncation but what would be a wish (get it) is to just set the format and have the anaylsis use that format.  For example if I have a bunch of data 98.12345543, 98.2234454,... and really it is good to 1 decimal point I can set that in the format, but it is still handled as 98.xxxxxxxxxx.  There are often times I use numeric values in nominal/ordinal ways and then I need to create a column to truncate.  I just want to move faster.

 

Data Handling.png

 

 

Ryan_Gilmore
Community Manager
Status changed to: Archived
We are archiving this request. If this is still important please comment with additional details and we will reopen. Thank you!