4 Replies Latest reply on Jan 2, 2018 9:49 AM by David Bull

# Calculating Time Between Two Dates in The New Year

Hello all,

I have a dashboard that shows the average time to finish a project. I have been using the following calculation to get this number:

DATEDIFF('weekday', [Ticket Received], [Ticket Completed]) -2 *

(DATEPART('week', [Ticket Completed]) -

(IF DATENAME('weekday', [Ticket Completed]) = 'Saturday'

OR DATENAME('weekday', [Ticket Received]) = 'Sunday'

THEN 0

ELSE 1

END)

The calculation calculates the time between the opening date and closing date taking only the work week into account (Saturdays and Sundays count as 0's in this calculation). When I opened the dashboard in the new year, the numbers were completely off for my projects that are still open or ended after 1/1/2018. Some times are in the 100's of days now even though they were opened in the week after Christmas.

Any help that could be provided would be greatly appreciated.

• ###### 1. Re: Calculating Time Between Two Dates in The New Year

Hi Aaron,

It's difficult to tell without seeing the workbook itself - but it could be because the DATEPART calculations will be returning 1 at the beginning of 2018, and 52 at the end of 2017. So "(DATEPART('week', [Ticket Completed]) - DATEPART('week', [Ticket Received]))"  for a project started at the end of last year and completed at the beginning of this year would return -51.

If that's not it, could you post a .twbx?

Thanks,

David

• ###### 2. Re: Calculating Time Between Two Dates in The New Year

David,

I attached the workbook to the post.

Thanks,

Aaron

• ###### 3. Re: Calculating Time Between Two Dates in The New Year

Hi Aaron

see the link to year end effects on the week number in Tableau

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Calculating Time Between Two Dates in The New Year

Hi Aaron,

It looks like it is stemming from the problem I mentioned above. The DATEPART elements of the calculation don't work across two years, because the 'week' datepart resets to one.

It looks like you're not being affected by the week 53 issue that Jim linked to - as none of your tickets were opened in the last week of the year.

Unfortunately, these weekday calculations can get quite complicated in Tableau. Have a look at this thread which offers some possible solutionsRe: DateDiff for Working days only  (the approach you're using is also proposed, but somebody points out that it might break when calculating across two calendar years).

Good luck!

David