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!

Data TablemacOSScriptingRe: 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

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