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

[#1]

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!

[#2 · Accepted solution]

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().

[#3]

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,
   :before_6_4_14 = "yes",
   :before_6_4_14 = "no"
)

[#4]

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.

[#5]

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

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

[#6]

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.