Date format in mmm-yy
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @mohit9garg,
can you post the format of your desired output? This will make it a lot easier to understand.
Best
Alex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @grossal
Please find attached here what I am getting with your kind help and what is my desired output.
Thanks,
Mohit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@grossal ... You are so cool 🙂
One last amendment please. Refer attached 🙂
Thanks,
Mohit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
