Change date format of field heading

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.

Date Time

Accepted answers

JoshuaGostick

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")

Dynamic Rename Dates.PNG

Hope this helps!

Josh

All comments

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

20191031 - EndOfMonth.yxmd

JoshuaGostick

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")

Dynamic Rename Dates.PNG

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703