Date format in mmm-yy

[mohit9garg]

I have date in dd-mm-yyyy format. I want to add a new column to convert the date in mmm-yy which should show the month and year. Also this should be in date format so that it should appear in chronological order in Crosstab (not in alphabetical order).

Can someone help me please. Thanks

[DavidP]

You can convert the date to that format with a formula like this:

Datetimeformat(DateTimeParse([date],'%d-%m-%Y'),'%b-%y')

but you can only save it as a Text datatype. Data datatypes have to have the standard date format

[DavidP]

If you can post some sample data, we can help you with the Crosstab issue. I think I understand the problem, but an example would make it easier to help.

[grossal]

Hi @mohit9garg,

as I was already working with some DateTime data, I thought I'd mock up a workflow for you:

Input-Format: 

What happens:

- Parsing the date in two new formats (Formula)

- Order them with the help of DateTime-object (Sort) - This is important for the ID generated afterwards

- I generate an ID based on the Formated Date field to help keep them in order after cross tab (Multi-Row-Formula)

- ID and Formated Date get combined (Formula)

- We shift the data (Cross Tab)

- We fix the column names (Dynamic Rename)

Result looks like this:

I'll attach the sample workflow. You'll probably have a Group By column (or multiple) in your real data and have to change this, but the workflow should give you an idea how to do it. Let me know what you think.

Best

Alex

[mohit9garg]

Thanks for your help Alex.

However this solution is working if I am taking month in 'Column Header'. What if I want to take Month in 'Group Data' in Crosstab.

Regards,

Mohit

[grossal]

Hi @mohit9garg,

can you post the format of your desired output? This will make it a lot easier to understand.

Best

Alex

[mohit9garg]

Hello @grossal

Please find attached here what I am getting with your kind help and what is my desired output.

Thanks,

Mohit

[grossal · Accepted solution]

We should have went this route earlier 😃

You can do this using a Formula and Multi-Field-Formula.

The Formula cleans up the Month Column with a Regex-Replace function:

Regex_Replace([Month],'(^\d+\s)','')

This will only replace the first number (no matter if one or two digits) and the space in front of the month name.

I am choosing a Multi-Field-Formula for the next one, because we don't want to add the same formula over and over. This is also dynamic for more columns in the future.

ToString([_CurrentField_]) + '%'

The workflow is attached. Let me know what you think.

Best

Alex 

[mohit9garg]

@grossal ... You are so cool 🙂

One last amendment please. Refer attached 🙂

Thanks,

Mohit

[grossal · Accepted solution]

You are welcome!

A Select-Tool will do the trick here:

Mark the column you want and use the arrows on top to change the order of the columns:

Sample is attached, but you'll probably just want to do it directly in your workflow.

Best

Alex