Col Formula Month()

[#1]

I have a data table where I have multiple date with times. I'm trying to change all the dates into just the month and maybe even a month-year format.

I know I've done this before by selecting the column going to formula and putting together Month(DateandTime) than I would use recode to change numbers into Jan. - Dec.

When I'm trying today, the Month() formula is changing my date to just dots. I've looked at other posts and the formula is what I'm suppose to be using. I'm not sure why I'm not getting my expected result?

[#2]

I assume the issue is that the column you are referencing in the Month() function is not a JMP date or date time value.  Could it be that the column referenced is a character string column and not a numeric?

If you could attach a sample of the data table you are working on it would be helpful.

[#3]

Hello Jim,

I have attached data table. That is another item that I found on another post. So I checked column info. and it says data type is numeric. I'm importing this data from excel but this shouldn't be an issue should it? I don't remember having this issue last time I did it, though I would say last time I was working with JMP12 and not 13.

[#4]

Thank you for attaching a file.

I had no issue in applying a formula to the data table you attached.

The formula I appled is

Month( :Date Time )

Given that the Date Time column is a proper JMP datetime column, I suspect you may have a scoping issue, where you are specifying "Date Time" and not ":Date Time".  Placing a colon in front of the column name, telling JMP to use a column named Date Time, and not a memory variable called Date Time

[#5]

Jim,

I was getting to ready to say it still wasn't working and got my screen shot ready.

 

I realized what the issue is. I was trying to use the formula on the same column I was referencing.

What I should have been doing was making a new column and adding the formula here.

Once I did that I had no issue.

I appreciate the help, I feel terrible because it was something so simple that I missed.

[#6]

Hi @Herrera5238,

On your dataset this seemed to work fine for me using the following formula:

Month( :Date Time )

Also, if you will be recoding to change the month numbers to month abbreviations, you could use the following formula directly:

Substr( Format( :Date Time, "Monddyyyy" ), 1, 3 )

I've attached your example data table here with those column formulae. 

@julian

[#7]

Julian,

What are the 1 and 3 doing for us in this formula?

What if I didn't want it abbreviated or abbreviated with the year? i.e Jan-18 or Jan-2018.

Is there a place I can reference the available formatting options?

Writing the formula including the format will save me that extra recoding step.

Thanks for the tip!!

[#8]

Hi @Herrera5238,

Substr() is a really useful character function that can pick out part of a string. The arguments of Substr() are shown in Help > Scripting Index:  

Substr(string, start, length)

So, the 1 and 3 in the example tell Substr() to take the string and return 3 characters starting at the 1st ordinal position of the string.The reason this works is that the rest of the formula I wrote  

Format( :Date Time, "Monddyyyy" )

returns a formatted version of the date based on the format specified ("Monddyyyy"). If you were to use just that function without Substr() you would get rows with values like: "Jan012018" . 

If you would like a non-abbreviated month you would need to do something different. We could start with: 

Format( :Date Time, "Date Long" )

which would return something like: Monday, January 1, 2018.   Since Substr() requires that we specify a specific starting position and number of characters, it won't be very useful to pick apart this string since the days of the week are different numbers of characters. But, we have many other character functions at our disposal! Word() is a great one -- this will return a particular word in a string based on some delimiter (default is a space, but could also be a comma, or both a comma AND space, which is what we need). Knowing this, we could use the following formula: 

Word( 2, Format( :Date Time, "Date Long" ), ", " )

The way to read this is: take the second word (that's the 2) from the formatted date time column (using Date Long),  and use as the delimiter of the words BOTH a comma, and a space (notice in quotes I have a comma and then a space--that's how I tell JMP to use both). This will return "January" for the first row in your data set. 

But, maybe you also want the year at the end, or beginning, or some intervening characters. We can do all of that using the Concatenating operator ||, or function Concat(). For example, say we want to put the year on the end of the string for each row. We need to get the year somehow (could be the Year() function pointing to :Date Time, or we could use Words() again, both would work). Here's one way to do this: 

Word( 2, Format( :Date Time, "Date Long" ), ", " ) || "-" ||
Char( Year( :Date Time ) )

which will return "January-2018." There is one thing I did here that you might not expect. I wrapped Year( :Date Time ) in the Char() function. This is important because Year( :Date Time ) will return a NUMERIC value of the year, and the Concat() function (or its inline operator version ||) requires character arguments. So, adding a simple Char() around Year() makes sure that Concat() is getting a character representation of the year to work with. 

There is so much you can do with character functions, and I encourage you to check out the Character section of the formula reference in Help > Scripting Index. 

I hope this helps!

@julian

[#9]

@Herrera5238

I strongly recomment that you familiarize youself with the Scripting Index.

     Help==>Scripting Index

Everyone of the functions that Julian and/or myself have shown in this discussion is listed in the Index, defined in the Index and example(s) is/are shown in the Index.

[#10]

what can i do if i want to creat season column from month?

---

@julian wrote:

Hi @Herrera5238,

Substr() is a really useful character function that can pick out part of a string. The arguments of Substr() are shown in Help > Scripting Index:  

Substr(string, start, length)

So, the 1 and 3 in the example tell Substr() to take the string and return 3 characters starting at the 1st ordinal position of the string.The reason this works is that the rest of the formula I wrote  

Format( :Date Time, "Monddyyyy" )

returns a formatted version of the date based on the format specified ("Monddyyyy"). If you were to use just that function without Substr() you would get rows with values like: "Jan012018" . 

If you would like a non-abbreviated month you would need to do something different. We could start with: 

Format( :Date Time, "Date Long" )

which would return something like: Monday, January 1, 2018.   Since Substr() requires that we specify a specific starting position and number of characters, it won't be very useful to pick apart this string since the days of the week are different numbers of characters. But, we have many other character functions at our disposal! Word() is a great one -- this will return a particular word in a string based on some delimiter (default is a space, but could also be a comma, or both a comma AND space, which is what we need). Knowing this, we could use the following formula: 

Word( 2, Format( :Date Time, "Date Long" ), ", " )

The way to read this is: take the second word (that's the 2) from the formatted date time column (using Date Long),  and use as the delimiter of the words BOTH a comma, and a space (notice in quotes I have a comma and then a space--that's how I tell JMP to use both). This will return "January" for the first row in your data set. 

But, maybe you also want the year at the end, or beginning, or some intervening characters. We can do all of that using the Concatenating operator ||, or function Concat(). For example, say we want to put the year on the end of the string for each row. We need to get the year somehow (could be the Year() function pointing to :Date Time, or we could use Words() again, both would work). Here's one way to do this: 

Word( 2, Format( :Date Time, "Date Long" ), ", " ) || "-" ||
Char( Year( :Date Time ) )

which will return "January-2018." There is one thing I did here that you might not expect. I wrapped Year( :Date Time ) in the Char() function. This is important because Year( :Date Time ) will return a NUMERIC value of the year, and the Concat() function (or its inline operator version ||) requires character arguments. So, adding a simple Char() around Year() makes sure that Concat() is getting a character representation of the year to work with. 

There is so much you can do with character functions, and I encourage you to check out the Character section of the formula reference in Help > Scripting Index. 

I hope this helps!

@julian

---