Convert date string to month

[Carlyn]

Hi

I have a string column  called effective date that has 202501 and I am trying to create a column that outputs Jan. I have a formula 'RIGHT([effective date],2)' which provides me with the 01 and now I need a formula to change the 01 to Jan. Can someone please tell me what formula it is I should use?

In addition, I then need to identify if the month is prior to the current month I am working on, so for this example, I am in May, but working on April data (will always be the month before) and I need to be able to identify if the result from the first part, ie Jan is earlier then April. 

Hope that makes sense. I have attached an excel to show what I have and what I am trying to get to.

Thanks

[Pilsner]

Hello @Carlyn 

One way to tackle this would be through the use of the date-time parse tool. 

I have used two date-time tools. One which converts your string "202501" to a date time format. To do this, I used the custom format with "yyyymm" to specify the 4-digit year and 2-digit month format. 

The second date time tool, converted the newly created date, back to a sting. Again, I used a custom output string format, and specified "Mon" which represents the first 3 letters of each month. 

I then used a select tool to drop the extra column created by the first date-time tool. 

I've attached the workflow below, please let me know if you have any questions. 

Regards - Pilsner

[cjaneczko]

This can be simplified by using the following formula. 

If your Datefield is a String

DATETIMEFORMAT(DATETIMEPARSE([DateField],"%Y%m"),"%b")

If your datefield is a number

DATETIMEFORMAT(DATETIMEPARSE(tostring([Datefield]),"%Y%m"),"%b")

[Carlyn]

Thank you ! Do you know what I can add to determine if the result is earlier than the previous month? So for example, if the result is Jan and I am running the flow in May, I want to know if flag if that row is earlier than Apr, whereas if the result was Apr, it would confirm it is current month data (we are always running it for prior month). Hope that makes sense

[Carlyn]

Thank you!

[abacon]

Here is way you can do it, this parses the date and adds a field that tells you if it's before the testing month (last month as requested).

[Pilsner]

Hello @Carlyn 

I've added a formula tool in which I believe achieves the result you're after:

The formula is designed to compare the first of last month with the date from your input. The datetimetoday() function means this should always be relevant to the current date. 

 

If you have any questions, please let me know.

Regards - Pilsner

[abacon]

@Carlyn If any of these solutions were helpful and solved your problem, please mark them as a solution so others may find it quicker.