how to take a month and year column and combine them into a single date column

[abajwa]

Hi

Sorry this must be a simple question but I am new to Alteryx hence asking it. I have a date set which has two column one for month and another for year. I need to combine them into one column as a date so for example 3-2015 or 3/2015 for the first entry in the example below. I need them to be a date so that when I run through I can arrange date in order of first to last date.

I have used this formula [MonthofYear]+"/"+[Year] to create a new column as a string. However I can't arrange the rest of the date from nearest time period to the last period as it is a string so when I run through all the other steps the data comes out as I need it but arranged in the wrong order.

If you could help that would be great.

Month	Year
3	2015
4	2015
5	2015
6	2015
7	2015
1	2016
2	2016
3	2016
4	2016
5	2016
6	2016
7	2016

[DataBlender · Accepted solution]

Hi @abajwa

Unfortunately it is not possible to have a date type field with the format of M-YYYY as it will tell you that this is not a valid date format (requires the day as well).

Therefore I'd suggest concatenating the strings as you have done but with the year first; this should allow you to sort them correctly.

[BenMoss]

You could do as DataBlender has suggested.

Alternatively you could develop your string further by adding '01/' in front.

So '01/'+[MonthofYear]+"/"+[Year]

You can now convert your string into a date format and then any sorting will work as needed.

Ben

[KaneG]

As has been shown above, there are multiple ways to deal with this.

Here is another. Create a new Date Field with the following formula:

ToString([Year]) + '-' + PadLeft(ToString(Month),2,'0') + '-01'

If you are using v11, then the following should work on your concatenated field (3/2015)

DateTimeParse([MonthDate],'%m/%y')

If not on v11, then you may need to PadLeft before creating your concatenated field (03/2015)

[marc_j]

Hm, this didn't work for me. I had [Year]+"/"+[Month Number] (both fields formatted as text strings and get the error "Formatted Date: "2017/8" is not a valid Date

[KaneG]

Hi,

Which of the above solutions are you trying to implement? If you want it to be a date type, then your string will need to have the format yyyy-mm-dd

[david_fetters]

You may want to add a PadLeft function to the month so you can be sure you're sorting correctly.  2018-06 will sort correctly but 2018-6 will not.

[marc_j]

Thank you for everyone's suggestions. What ultimately worked for me is a
formula that created a concatenated Date column, i.e. [Month Number] +
"-1-" + [Year] and then a Convert Date from tool (from the format
MM/dd/yyyy)

Later I found out that you don't even need the day, you can just
concatenate the date to be [Month],[Year] and Convert Date from Month, yyyy
format

Learning!!

--
*Marc A. Janke*

PwC | Senior Associate