Change date format of field heading

[muns]

Hello, I'm new to Alteryx so this is most likely a basic question (apologies).

My input data is from two Excel sheets. The top sheet has the date set at month start (1980-12-01) while the bottom sheet has month end (1980-12-31). How do I make them the same e.g. change all xx-xx-01 to be xx-xx-31?

Many thanks.

[Jonathan-Sherman]

Hi @muns,

I would use this formula which converts all dates to be at the end of the month DateTimeTrim([Date],"lastofmonth")

If this solves your issue please mark the answer as correct, if not let me know! I'v attached my workflow for you to download if needed.

Regards,

Jonathan

[JoshuaGostick · Accepted solution]

Hi @muns,

Connect a Dynamic Rename tool to your data. Then, select the fields that you would like to change the names of.

Finally, under Expression, add an expression that changes the date values such as the one below:

REGEX_REPLACE([_CurrentField_],"(\d\d\d\d\-\d\d)(\-\d\d)","$1-31")

Hope this helps!

Josh

[muns]

Thanks Josh, this worked perfectly!

[muns]

Thanks Jonathan - I will use this elsewhere but in this case my dates were the field heading so I couldn't get this column formula method to work (I guess I would need to transpose the data, use the formula then transpose back).

Thanks very much for the response though - much appreciated!

[Jonathan-Sherman]

Hi @muns,

Sorry I misread the question! I've amended my solution to solve what you were actually looking for. This solution will amend each header to be the last day of that month rather than 31st, depends which you are looking for.

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan