Hi guys,
My first post to the Tableau Community so I apologies if I've mislabeled the questions or anything like that.
I'm currently having problems reading some data in Tableau which is an export from Data Workbench (see attachment). The date is currently formatted MMM'YY, and I want to change create a calculated field (or something else) that can format this into a format Tableau can read. Unfortunately we can't change this in Data Workbench, so I have to do this manually in Excel. Changing it from say "Nov'16" to "Nov-16".
Thanks
Chris
hi Christoffer,
As it turns out it doesn't actually matter (I forgot Excel now supports DATEPARSE!)
So the formula to turn this to a date is
DATEPARSE('dd-MMM-yy', '01-' + LEFT([Month],3)+'-'+TRIM(RIGHT([Month],2)))
The first part 'dd-MMM'yy' tells Tableau what format to expect (notice I've added a 01 for the day, as the date needs to have a day part to it). I then use the String manipulations to turn your string into that format. btw you may not need the TRIM...I just seem to have ended up with an extra (space) character somewhere!!
you might also find this useful
Formatting Dates and Times - ICU User Guide
This is how I knew that 3 capital Ms (MMM) was the month format of the 1st 3 letter format of a month.
Attached (9.3) is an example running live off an Excel.
Hope that does the trick and makes sense, but let me know if not.
