Subscribe Bookmark RSS Feed

Date Issue

Steven_Moore

Super User

Joined:

Jun 4, 2014

I am having some issues with creating a clolumn of dates.  For example, entered 01/2010 and 02/2010 in the first two cells of a column.  I then highlight the two cells and right click.  Then I select "Fill" and "Continue sequence to..." and then select line 65.  This seems to work well until I discovered that a month somewhere in the column is skipped!!!!  What is up with that?  Is there some sort of bug?  Repeated trials of this produced the same results in different columns, though different mopnths are skipped in different columns. Data Type is Numeric.  Modelling Type is continuous, and m/y is selected

Steve
1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The difference between dates is internally an exact period of time (31 days for those specific dates) whereas the length of a month varies. So skipped months are expected here.

 

Try a column formula instead:

Date Increment(Informat("1-2010", "m-y"), "month", Row() - 1)
8 REPLIES
Dan_Obermiller

Joined:

Apr 3, 2013

Actually, JMP is likely doing everything perfectly correct, but the results are not expected. Remember that dates are truly stored as a number (number of seconds since January 1, 1904). When you type 1/2010 and 2/2010 JMP is storing those as numbers (3345148800 and 3347827200). The difference between those is 2678400 seconds. When you choose to continue the sequence, JMP continues adding 2678400 seconds to the previous value. Eventually the pattern will skip a month because there are not the same number of seconds in each month.

You can see this by changing the format to m/d/y and noticing that you are not always getting the first day of the month. You may find it easier to "build" your date column by specifying the month, day, and year separately and then combining them.

Dan Obermiller
Solution

The difference between dates is internally an exact period of time (31 days for those specific dates) whereas the length of a month varies. So skipped months are expected here.

 

Try a column formula instead:

Date Increment(Informat("1-2010", "m-y"), "month", Row() - 1)
Dan_Obermiller

Joined:

Apr 3, 2013

I like this formula even better than my suggestion of building the date by combining columns.

Dan Obermiller
connect01

Staff

Joined:

Oct 23, 2012

The format of "m-y" does not exists. When updating the formula to the format shown below, it works well.

 

Date Increment(Informat("1/2010", "m/y"), "month", Row() -1)

 

ms

Super User

Joined:

Jun 23, 2011

Sorry for the confusion. I think it's a localization issue/feature. For me "slash formats" don't exist.

8869_dateformats.png

connect01

Staff

Joined:

Oct 23, 2012

@MS Which locale are you using?

ms

Super User

Joined:

Jun 23, 2011

For most of the time I'm running English JMP on Swedish configured Mac OS X.

connect01

Staff

Joined:

Oct 23, 2012

Thanks.