Are the dates that you want to find the difference intervals between in your data? If so, they should be in mm/dd/yyyy format in Excel, so that they are recognized as dates within Tableau. You will be able to tell if Tableau recognizes them as dates, as they will have a calendar symbol next to the them in the data box. However, if the calculated fields are being written and a date that is not in the data (or a specific date) wants to be included in the field, then the date should be enclosed with pound (#) signs.
For your last question, is the outcome supposed to be a date? If so, a calculation similar to the following could be used:
date(str(datediff('month', #July 1, 2012#, #August 1, 2012#)) + '/' + str(datediff('day', #July 1, 2012#, #August 1, 2012#) )+ '/' +str(datepart('year', #July 1, 2012#)))
Or do you just want the number of days, months and years? If this is the case, something like this could be used:
str(datediff('month', #July 1, 2012#, #August 1, 2012#)) + ', ' + str(datediff('day', #July 1, 2012#, #August 1, 2012#) )+ ', ' +str(datediff('year', #July 1, 2012#, #August 1, 2012#))
Hope this helps a bit!
Awesome! Thanks for your help!
I do have all of the dates in the Excel files and they are formatted as mm/dd/yyyy format. I have about 8,000 records and each record has 4 dates so adding the "#" manually is not going to work. If Tableau imports and recognizes the data fields as dates, do I even need to add the # to make it a literal?
If I do need to append each date with #, is there an easy way to do it in Tableau? How?
As for the calculation, I need the result to be in the Year, Month, Day format so your second example is perfect (I'm guessing it won't change the calculation to rearrange the DATEDIFF in Year, Month, Day order).
I'm not a coder (not even close!) so the syntax is a little confusing to me, is there a resource that you suggest that would help me become more comfortable with how calculations are structured? I really don't know what the difference is between and string and an expression or why I'd use one over the other and I hate to ask accomplished users to spoon feed me.
Thanks again for your examples, this community is so helpful!
No need to add the # signs if your dates are in your data, as mentioned, it would only need to be done if you were trying to choose a very specific date.
You are right about the order, you can easily rearrange the calculation to get it in the desired order.
In terms of learning calculations, you are in luck as there is a free live online training on Wednesday (June 6) for calculations:
Other good resources are the product manuals, the forums, and trial and error!
Thanks for the suggestions, I'll check out the class on Wed.
Trial and Error for me is resulting only in Error, it's very frustrating. The calculation that you wrote for me isn't working and there's no way I'd ever be able to come up with anything close.
In the Excel spreadsheet, Start Date and End Date are date values in mm/dd/yyyy format
str(datediff('year', [Start Date], [End Date])) + ', ' + str(datediff('month', [Start Date], [End Date]) )+ ', ' +str(datediff('day', [Start Date], [End Date]))
The results are displaying in this format: 3, 37, 1131 which seems to be a datepart although the calculated values are wrong.
Thanks again although I'm not sure where to go from here.
Would you be able to post your workbook (make sure to save it as a twbx file)?