11 Replies Latest reply on May 18, 2019 5:29 AM by Tom Szelagowski

# DATEDIFF not giving correct number of days difference

DATEDIFF('day',[Date Opened],[Date Closed])

When using the above equation, the calculation is not being done as I would like. The equation only gives the difference between the days and does not take into account the month or year ("dd" difference from mm/dd/yyyy). For instance, the equation would give me 1 day difference between 7/1/12 and 9/2/12 because the day fields are only 1 number apart. In reality, there are 2 months between the dates, so it would be 64 days. How do I fix this in Tableau (without making the calculation in Excel first)?

• ###### 1. Re: DATEDIFF not giving correct number of days difference

Create a calculated field : round(date field 1] - [date field 2], 0) if you're looking to calc. the difference in dates for a table of dates

1 of 1 people found this helpful
• ###### 2. Re: DATEDIFF not giving correct number of days difference

Hi James,

The calculation that you are using (with DATEDIFF) should give you the total number of days. What does your view look like?

-Tracy

• ###### 3. Re: DATEDIFF not giving correct number of days difference

When you say "day fields" does that mean you have a different field for Month Day and Year?

So like:

 Month Day Year 7 1 2012 9 2 2012

 Date 7/1/2012 9/2/2012

If so, you can create a calculated field to turn your 3 fields into a date, then use the DATEDIFF function.

DATE(
STR([Month])// month#
+'/'+
STR([Day]) // day#
+'/'+
STR([Year]) // year#
)

If you were doing datediff of 1 and 2, you would get 1 because 1 is 1/1/1900 and 2 is 1/2/1900...

• ###### 4. Re: DATEDIFF not giving correct number of days difference

I have a Problem with DATEDIFF function . I used it several time and gave me accurate numbers and now when I want try it in different worksheet it is not, even I tried a simple example, DATEDIFF('month', #July 15, 2004#, #April 15, 2004#), and still the same. Dont know what to do and need this function very bad

• ###### 5. Re: DATEDIFF not giving correct number of days difference

What result are you getting for:

DATEDIFF('month', #July 15, 2004#, #April 15, 2004#)

And, what result do you think you should be getting?

--Shawn

• ###### 6. Re: DATEDIFF not giving correct number of days difference

I am getting different results in different worksheets for example -1719. However, as you may know I should get the result of -3. I used this function a lot but I don't know what is wrong with it now.

• ###### 7. Re: DATEDIFF not giving correct number of days difference

I have noticed lately that Tableau gets confused sometimes. The weird part is that it keeps working but behaving strangely. For instance this morning I was toggling some one-click sorts on a 3-panel bar chart and at one point the bars all quit sorting, but the dimension (row label) would re-sort, so the bars and labels were completely out of sync. I solved my problem by closing and reopening the workbook. This was a long way of saying always try the old close/open trick first.

If you can post a sample workbook, I can take a look at it for you.

--Shawn

• ###### 8. Re: Re: DATEDIFF not giving correct number of days difference

Thanks for your quickresponse Shawn. I have attached the workbook.

• ###### 9. Re: Re: DATEDIFF not giving correct number of days difference

You're using SUM(). You need to use a non-additive aggregation like MIN() MAX() AVG() or MEDIAN(). These will all return the -3 you are looking for.

--Shawn

7 of 7 people found this helpful
• ###### 10. Re: Re: DATEDIFF not giving correct number of days difference

Thanks Shawn . It resolved my problem