topic Re: Working with dates in Discussions

Working with dates

SCWong — Fri, 08 May 2020 15:13:15 GMT

Hi, I have a set of data with information from 2010 to 2020. I'm trying to create a formula column for data before and after 2012 so that that I can compare these 2 sets of data. How do I manipulate the date data? Thanks.

Re: Working with dates

txnelson — Fri, 08 May 2020 16:28:43 GMT

If your date column is a JMP date value, and lets assume the date's column name is "The Date", then creating a new formula column, formula is fairly simple

If(Year(:The Date) < 2012, 1, 2)

1 would be assigned for all rows where The Date is less than 2012 and a 2 will be assigned for 2012 and greater

Re: Working with dates

SCWong — Fri, 08 May 2020 16:51:47 GMT

Thanks! What if it's a specific date for a year?
On the same topic, the set of data is rather skewed, with daily data from 2010 to 2020, and minute data for a specific day. In the graph builder - how would I be able to analyse the set of data for that specific day vs the rest? I tried to drag date into "group by x", but it doesnt seem to give me options to specifically group them. I can probably manually set a row state to these specific data, but I was wondering if there was a more direct way to work that out.

Re: Working with dates

txnelson — Fri, 08 May 2020 19:34:11 GMT

First, I suggest you take some time to read about JMP date, time functions and formats. It really will help you with the kinds of things you are looking to do. So taking what you asked for your first question in your response, you can use the informat() function to specify any particular date or date time for evaluation. So taking my last format and making it more specific, it would be:

If( :The Date < informat("09/23/2012", "m/d/y" ), 1, 2 );

If you want it to be down to an hour, minute, second, you just have to specify it, along with the JMP format that is required to read it.

For your analysis of one day vs. all other days, I would simply create a new column or a new transformation using the following formula

If( informat("09/23/2012 23:59:59", "m//d/y h:m:s")  < :The Date < 
informat( "09/24/2012", "m/d/y"), 1, 2 );

Re: Working with dates

Jeff_Perkinson — Fri, 08 May 2020 19:46:11 GMT

@txnelson has steered you correctly. has good information about these topics and will likely be useful for you.