Converting first word of a column header to Date

[nopaul]

Hello,

We are needing to create a Date variable (e.g. 2018-07-01) given the first word of a column (e.g. July Sales).  

Count	July Sales	Date
1	24	2018-07-01
2	23	2018-07-01
3	22	2018-07-01

Any help is appreciated. 

Thank you!

[CharlieS]

@nopaul

In the attached solution: I use a Dynamic Select tool to identify the column with "Sales" and then a Field Info tool to get the column name. After that, the name is parsed and converted to a date field which is appended to the original input.

This works in this case, but if the scenario includes multiple month columns, or occurs in a year other than 2018, some modifications will be necessary.

[jkoh · Accepted solution]

This is a simple workflow that would accomplish the task.  You basically use the transpose tool, grab the first word of the header with the text to columns tool, convert to a date, and then put everything back in the right place with the cross tab tool.

If you wanted to convert the headings for more than one column then it gets more complicated.

[Greg_Murray]

Here is another solution. Like the one above, you'll need to make a few changes for it to work with multiple month columns.

[scottj]

Same idea, but I created it as a Macro.  Transpose the data to get fields in a column, then I used a Find/Replace to match to all possible months.  Build out date with a Formula tool and Dynamically rename.  You could also Crosstab back, then Union to the original keeping the order, followed by a sample to remove the extra generated row.