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

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

JonathanL — Sat, 10 Jun 2023 23:46:45 GMT

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!

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

jthi — Mon, 04 Apr 2022 17:42:04 GMT

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

Jeff_Perkinson — Mon, 04 Apr 2022 20:01:52 GMT

Take a look at . You can use a date constant for comparisons like this.

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

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

Craige_Hales — Tue, 05 Apr 2022 01:36:26 GMT

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

Mark_Bailey — Tue, 05 Apr 2022 12:12:16 GMT

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

JonathanL — Wed, 06 Apr 2022 01:13:56 GMT

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.