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; } } }

Bug in Creating a Column for Date Splitting

tnodd

Hello,

The problem is very simple and I am able to work it out, but somehow theere seems to be a bug in the output. So wondering if there is an error in my formula?

I have a JMP table with a time Col in the format as (mm/dd/yyyy h:m:s AM/PM)

dt << New Column( "DATE_FLAG",
    Character,
    Nominal,
    Formula( If( Abbrev Date( :Name( "TIME_COL@ABC" ) ) <= Abbrev Date( 22Jul2020 ), "before", "after" ) )
);

Now finally in my updated table I am getting the DATE_FLAG Column and before/after values. But some of the values/rows) which should be before say the dates are before 22nd July 2020 are still labelled as after? The rest of the rows are labelled as expected.

So what is wrong? Do I have to specify the date time format? If so how can I ?

I think I figured out the problem, but need some help with that:

The Abbrev Date comparison is comparing the two Character Date strings and that's why when 07/04/2020 10:12:34 AM is converted to 07/4/2020 when compared with 07/22/2020 is giving after instead of before.

How can I fix this?

Thanks

Accepted answers

Jeff_Perkinson

There's no need to do any conversion to anything else to compare date values.

Just use a date constant in your comparison by specifying the date in ddMonYYYY format:

dt << New Column( "DATE_FLAG",
	Character,
	Nominal,
	Formula( If( :Name( "TIME_COL@ABC" ) <= 22Jul2020, "before", "after" ) )
);

All comments

tnodd

I think the issue is fixed by replacing Abbrev Date with Short Date. Abbrev Date converts it to a character, but ShortDate keeps it in the Date Time Format, which makes the correct comparison.

Jeff_Perkinson

There's no need to do any conversion to anything else to compare date values.

Just use a date constant in your comparison by specifying the date in ddMonYYYY format:

dt << New Column( "DATE_FLAG",
	Character,
	Nominal,
	Formula( If( :Name( "TIME_COL@ABC" ) <= 22Jul2020, "before", "after" ) )
);