-
1. Re: Date String Conversion
Joe OppeltMay 14, 2015 9:35 AM (in response to Jonathan Kirkpatrick)
You need a "day" portion in there too.
So do
MID([srting field], 1, 3) + "/1/" + MID([string field], 5,4)
-
2. Re: Date String Conversion
Ken Patton May 14, 2015 9:46 AM (in response to Jonathan Kirkpatrick)Make a Calculated Field using DATEPARSE like
DATEPARSE("MMM-yyyy",[yourDateField])
-
3. Re: Date String Conversion
Ken Patton May 14, 2015 9:47 AM (in response to Joe Oppelt)If DATEPARSE can be made to work correctly, then inserting the Day value explicitly should not be required.
(Note I said "if" and "should" )
-
4. Re: Date String Conversion
Rody Zakovich May 14, 2015 9:52 AM (in response to Jonathan Kirkpatrick)If you need help with various date formats and the DATEPARSE function, here is a helpful link.
http://kb.tableau.com/articles/knowledgebase/understanding-the-dateparse-function
Regards,
Rody
-
5. Re: Date String Conversion
Jonathan Kirkpatrick May 14, 2015 11:37 AM (in response to Ken Patton)Hi Ken,
I tried that and i get all Null values.
Jonathan
-
6. Re: Date String Conversion
Ken Patton May 14, 2015 11:56 AM (in response to Jonathan Kirkpatrick)Could you post a sample of the data? Maybe 10 rows with the field formatted exactly has you get it in Tableau?
I tested by making a 3-line Excel that looks like
Month
'Apr-2011
'Nov-2013
and that worked fine with DATEPARSE as given here. But the relevant thing is what does your data look like when it goes into Tableau?
-
7. Re: Date String Conversion
Jonathan Kirkpatrick May 14, 2015 12:01 PM (in response to Ken Patton)Here is the flat data
-
image001.png 107.4 KB
-
-
8. Re: Date String Conversion
Ken Patton May 14, 2015 12:11 PM (in response to Jonathan Kirkpatrick)Okay yes that helps, but a screen shot is not the same thing as a data sample.
Please look at the attached, to be sure that this works.
One thing you should also know about DATEPARSE is that it is very Case Sensitive in its use of the formatting string. That is by ICU convention. So you can't just mix and match upper and lower case for the Y and M bits.
-
converteddates.twbx 17.3 KB
-
-
9. Re: Date String Conversion
pooja.gandhi May 14, 2015 12:13 PM (in response to Jonathan Kirkpatrick)Jonathan,
You are getting 'nulls' because the dateparse formula is converting the 'contents' of the date column(s) you have in your data. For dateparse to work, you need all the date strings in one column so that when you choose to dateparse that column, it actually converts those strings to date type:
Your best bet here may be to reshape the data using the add-in Installing the Tableau Add-In for Reshaping Data in Excel | Tableau Software
and bring all date strings into one column and then choose that column to parse the date, so that you data looks something like this: