Separating "Q#YYYY" columns into "Q# YYYY" and removing any columns > 12 months from today

[cslagle005]

Hello, 

In my sample data set, I'm trying to achieve 2 things:

1. Add a space between each instance of the pattern "Q#YYYY".  So for example, "Q32023" would become "Q3 2023".
2. Remove any column that displays a Quarter that is more than 12 months away.

I can get step 1 to work with Regex Replace, sort of. I think the challenge is that this file would be pulled monthly, so the particular Q# and YYYY will continue to change as time passes so the method for this must be dynamic. Additionally, the way I'm doing it right now transposes the data and then crosstabs it back, but that's resulting in the data getting messed up in the process (user error, I imagine).

I'm not sure how to approach step 2 because it seems like I'd need to possibly convert the "Q3 2023" into a date/time format like "09-30-2023" and then create an IF formula to compare today's date to the column date and remove the column if conditions are met.  Then, format the MM-DD-YYYY back into the previous Q# YYYY format after.

Workflow is attached, thanks so much for any insight you can offer!

[binuacs]

@cslagle005 For your first scenario use the dynamic rename tool to update the heading to replace the '_' with ' '

For the second scenario can you provide the expected result? I mean which quarters are you expecting in the result?

[binuacs]

@cslagle005 Another method of doing this for renaming your headers

[SPetrie · Accepted solution]

You can put the regex expression in a dynamic rename as well instead of using the transpose. 

To dynamically remove columns, I like to use a combo of dynamic rename to update the descriptions, and then a dynamic select to kill them off.

You can use the updated field names to do the calculations for what dates to remove.