475999_en-US

liliconcom

Checking if date column value is before/after a given date

I am using JMP pro 15.

 

I have a column in my dataset "MRI_Date" which has values in the format MM/DD/YYYY. I would like to create a new formula column checking whether each value of MRI_Date is before or after the date 06/04/2014 and return "yes" if before and "no" if after.

 

I tried this formula but it is not returning accurate results:

 

If( :MRI_Date < "06/04/2014",
:before_6_4_14 = "yes",
:before_6_4_14 = "no"
)

 

The name of my formula column is "before_6_4_14".

 

Can someone recommend a solution to this issue? Possibly the format of my MRI_Date column?

 

Thank you!

Consumer and Market ResearchmacOSSharing and Communicating ResultsRe: Checking if date column value is before/after a given date

Thank you to everyone who replied. I ended up converting the format of my date column to "Best" as jthi suggested which converted each date value into seconds from Jan 1, 1904. This made it easy to compare with the date I was interested in.

Re: Checking if date column value is before/after a given date

Since you are using a column formula, you can abbreviate @Jeff_Perkinson's solution with this formula:

 

If( :MRI_Date < 04JUN2014, "yes", "no" )

Re: Checking if date column value is before/after a given date

if MRI_Date is a character column the comparison won't be correct (year is in least important position, etc). When you use the column properties to convert the column to a proper date column, be sure to change Numeric AND Format at the same time. If you change to numeric without the proper date format, you'll likely see all missing values.

 

Character columns tend to be left-aligned and numeric columns right-aligned. Dates are numeric, number of seconds since 1904.

Re: Checking if date column value is before/after a given date

Take a look at Using dates, times, datetimes and durations in JMP. You can use a date constant for comparisons like this. 

If( :MRI_Date < 04JUN2014,<BR />   :before_6_4_14 = "yes",
   :before_6_4_14 = "no"
)

Re: Checking if date column value is before/after a given date

I would start testing out that you are using correctly formatted dates. Change the Format of your date column to Best and see if you get JMP's DateNum (something like 3731949420, which is number of seconds since midnight, January 1, 1904). If you get something different, you might have to do some conversions.

 

"06/04/2014" isn't correct date in JMP, you can for example use Date MDY() (Date MDY(06,04,2014)) or InFormat() (Informat("06/04/2014", "MM/DD/YYYY")) to convert that to a date. I would use Date MDY() as it should always work.

 

After that you should be able to do the comparison like you are doing, or you could use something like Date Difference().

Re: Checking if date column value is before/after a given date

I had this same question today. Just want to point out to anyone in the future reading through this string that Mark_Bailey's suggestion works. I implemented this as the simple solution to my equation.