nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

JonathanL

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!

Accepted answers

jthi

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

All comments

jthi

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

Jeff_Perkinson

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

Craige_Hales

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.

Mark_Bailey

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

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

JonathanL

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.

TroyLarsen1

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.