1 2 Previous Next 28 Replies Latest reply on Nov 4, 2019 12:20 PM by Joseph Hall

# DateDiff for Working days only

Hi there,

As the title reads, is there a function to work out the date differences which excludes weekends?

I'm not sure if DateDiff does this.

Thanks

• ###### 1. Re: DateDiff for Working days only

Here's a thread with some solutions:

1 of 1 people found this helpful
• ###### 2. Re: DateDiff for Working days only

There is also a calculation I have created - I'm sure it could be made more simple with some clever thought, but given a [date] and [start date], we first work out the day of the week of these:

[Weekday Start]  datepart('weekday',[Start Date])

[Weekday End]  datepart('weekday',[Date])

First the whole weeks we must add (whole weeks times 5)

int(([Date]-[Start Date]-1)/7)*5

plus the extra days, wholly dependent on the day of week of start and end:

case [Weekday End] when 1 then

if [Weekday Start]>5 then 7-[Weekday Start]

elseif [Weekday Start]=5 or [Weekday Start]=4 then 2

else 6-[Weekday Start] end

When 2 then

if [Weekday Start]=7 or [Weekday Start]=1 then 1

elseif [Weekday Start]=5 or [Weekday Start]=6 then 2

else 7-[Weekday Start] end

When 3 then

if [Weekday Start]>=3 and [Weekday Start]<=5 then 8-[Weekday Start]

else 2 end

When 4 then

if [Weekday Start]=4 or [Weekday Start]=5 then 9-[Weekday Start]

elseif [Weekday Start]=3 then 2

else 3 end

When 5 then

if [Weekday Start]=4 or [Weekday Start]=3 then 6-[Weekday Start]

elseif [Weekday Start]=5 then 5

else 4 end

When 6 then

if [Weekday Start]=5 or [Weekday Start]=4 then 7-[Weekday Start]

elseif [Weekday Start]=2 or [Weekday Start]=3 then 4

else 5 end

When 7 then

if [Weekday Start]=6 or [Weekday Start]=5 then 7-[Weekday Start]

elseif [Weekday Start]=3 or [Weekday Start]=4 then 3

elseif [Weekday Start]=2 then 4

else 5 end

end

This gives exactly the same answers as networkdays in Excel, but with no possibility of adding holidays as well.

1 of 1 people found this helpful
• ###### 3. Re: DateDiff for Working days only

Thanks. I found this working for me:

DATEDIFF("weekday",[Calculation1],[Calculation2])-2*(DATEPART('week', [Calculation2]) -DATEPART('week', [Calculation1]))

• ###### 4. Re: DateDiff for Working days only

Are you sure that calc is correct? I don't think it works properly, and doesn't work across years/jan-dec split

• ###### 5. Re: DateDiff for Working days only

Yes it seems to be ok. The only problem it seems to have when using dates from two different data sources (blended data). There seems to be so much restrictions when it comes to blending data. Is there a way around this problem?

Thansk

• ###### 6. Re: DateDiff for Working days only

Hi I don't think this works perfectly yet. If you have the start and end date the same, the networkdays = 5. Needs some tweaking

Has anyone added/seen a larger calculation that takes this into account along with the times (minutes preferably) with a lookup to a holiday table or data set?

• ###### 7. Re: DateDiff for Working days only

Alex,

Thanks for your post on 5/15/12 with the calculation to count workdays between two dates.  Most calculations I've found use DateDiff then subtract weekends, but those aren't accurate when weekends span between two months.  Yours was the most accurate calculation I've found so far, but it was slightly off, maybe because I was using it to calculate work days from the start to a midpoint in the month instead of for the entire month.

Using your calculations as a starting point, I created a new method that worked exactly for me, so I wanted to post it in case it can help anyone else.  I included comments below with "//" to help explain how it works.

WeekdayStart

// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

DatePart('weekday',[Start Date])

WeekdayEnd

// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

DatePart('weekday',[End Date])

Work Days

// (1) Calculate number of whole weeks between dates, then convert to # of Work Days

INT(([End Date]-[Start Date])/7)*5

+

// (2) If sum of the first and last week days is a full week, then it was accounted for above,

//     so we need to subtract those work days because we will calculate work days for the

//     first and last weeks in the next two steps

IF

// # of total days in 1st Week

8-[WeekdayStart]

+

// # of total days in Last Week

[WeekdayEnd]

> 7

// Subtract week of work days from step (1) which will be calculated in steps (3) & (4) below

THEN -5

ELSE 0

END

+

// (3) Add # of Work Days for 1st Week

// If 7 Days, then subtract 2 for Sun & Sat

IF 8-[WeekdayStart] = 7 THEN 8-[WeekdayStart]-2

// If 1 Day and it's Sunday, then exclude

ELSEIF 8-[WeekdayStart] = 1 AND [WeekdayStart] = 1 THEN 0

// Otherwise subtract the one Sunday

ELSE 8-[WeekdayStart]-1

END

+

// (4) Add # of Work Days for Last Week

// If 7 Days, then subtract 2 for Sun & Sat

IF [WeekdayEnd] = 7 THEN [WeekdayEnd]-2

// If 1 Day and it's Sunday, then exclude

ELSEIF [WeekdayEnd] = 1 THEN 0

// Otherwise subtract the one Sunday

ELSE [WeekdayEnd]-1

END

7 of 7 people found this helpful
• ###### 8. Re: DateDiff for Working days only

Nice job Lynn.

• ###### 9. Re: DateDiff for Working days only

Hi Intellectuals,

Before going through solution I tried to make understand the requirements and solution approach.Please make me aware if found any flaws in my solution(even for specific cases).

## What's the requirement/challenges n caution to take=

• We need to find business hour of an employee
• For holiday calculation-custom sql required (since blending or joining can’t performed)
• Working hour of an employee is 9AM-6pm and sat-sun are taken as weekends here
• Business days=total days elapsed in date range - all weekends - holidays in that date range+ all those holidays which falls on sat or sun
• So,exclude sat/sun from holidays becz all weekends taken out already

• Actual holidays=[all holidays in date range] - [Exclude holidays which falls on weekends]
• Calculating missing hr means Hr. difference calculation corresponding to each start time and end time  as office is b/w 9-6
• Business hrs means all working hours of days(exclude net holidays n weekend)
• Business hours= [(total hrs elapsed - missing hrs)]

• There is a list of holidays(although I've given this only for year 2012,You can add more dates of other years)
• Must check on aggregation (ie. sum,avg,min etc.) when taking measures in the view by dragging(why because records are kept in that way,view data to visualize this situation).
• All calculated field have given comments for better understanding(it'll tell u how it works)

# So,APPROACH to accomplish tasks is=====

1>custom sql required to relate both sheets.

2>calculation of  weekdays = days without weekends (take avg aggregation when dragging this measure)

3>holiday calculation in given date range

4>exclude sat/sun from holidays becz all weekends taken out already

5>find actual holidays

7>calculation of missing hours (take min aggregation when dragging this measure)

9>Utilization calculation

See TWBX file for details.

my solution can also be referred on this thread

http://community.tableau.com/message/322255#322255

It's works very perfect for all cases/conditions

• ###### 11. Re: DateDiff for Working days only

Well Lukasz,

I've seen your solution,very complicated fields were written although useful and highly appreciable because I may ned to use the similar formulae in my other requirements.

Anyways,this solution is very useful but in context of my raised ticket I found it not solving  my requirement exactly.

Few things I want to have clarified from your side.

1)As you said

"Your calculation in the first row says 93 as "business hours" - I reckon this should be 84:"

In order to justifying what I wrote please check 1st row manually in calender (forget calculated fields for a while)-

Since date range is too small that you can check this from calender.(Involve 26/3 and 9/4 also)

See Ashwin is working from 26 march to 9 april-2014.So,it's clear excluding sat/sun it's exactly 11 working days.

So,11 days X 9 hrs=99 hrs (since employee works 9hrs a day ie. 9AM-6PM that is fixed)

Now,consider arrival and departure time while calculating how much hr. he missed

as start time is 3PM(ie. 1500 hrs) so,he missed 6 hrs office (as he supposed to come at 9AM)

and end time 1AM,it doesn't make any difference(since leaving 1 at night is not taken in calculation as all time before 9AM and after 6PM for arriving and leaving office is not counted)

So,Ashwin in totality missed 6 hrs.

2)Now,in your calculation let's take eg. of employee Suman

see only 1 day holiday comes in his tenure,that's fine,agreed.

So, accordingly corresponding "holiday time" would be 1day x 24 x 60 x 60=86400 seconds

but,in that place 32400 is computed as result in your twbx.

3)So,please clarify how holiday time and work time is calculated means how it can be associatted with requirement.(brief the approach)

4)Also,tell you made solution dynamic by supplying parameters

what's purpose of parameters sunend,satend,sunstart etc.

• ###### 12. Re: DateDiff for Working days only

1) No, it is 9 full business days and 1 with 3 hours, hence 9x9+3

2) It is subtraction of business hours during holiday from the work time calculated as if there was no holiday, hence 1x9x3600 = 32400 seconds

3) Refer to my original post that I linked

4) Those parameters come to play when there is business over weekend but with different start/end hours than on weekdays, e.g. you work 9-18 Mon-Fri, 10-17 Sat and 10-14 Sun

• ###### 13. Re: DateDiff for Working days only

Lynn,

Even your logic needs slight change.

When the start date and end date are exactly seven days apart but the value of int([end date]-[start date])/7=0, the logic falls.

• ###### 14. Re: DateDiff for Working days only

Since the topic re-emerged after more than a year and the problem still does not seem to be properly solved I want to share my solution. It is very similar to what I mentioned above but deals with dates (not datetime) and counts days (and not seconds).

1. Attached is a t-sql function as I believe this should be calculated at the source, e.g.

```select a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) calc1, a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6) calc2
, MIN(dbo.NetworkDays(d1, d2, 6,7))-SUM(dbo.NetworkDays(b.date, b.date, 6, 7)) Business_Days
, COUNT(b.date) h_cnt, min(b.date) min_h_date, max(b.date) max_h_date, min(b.name) min_h_name, max(b.name) max_h_name
from NETDAYS a left join (select * from COMREF.dbo.public_holidays where ISO_COUNTRY_CODE_SHORT='PL') b
ON a.d1<=b.date and a.d2>=b.date
group by a.d1, a.d2, a.NETWORKDAYS_1, dbo.NetworkDays(d1, d2, 6,7) , a.NETWORKDAYS_2, dbo.NetworkDays(d1, d2, 5,6)
```

2. A tableau equivalent of the row level function I created looks as follows: As its name suggests this is supposed to be equivalent of excel NETWORKDAYS.INTL and you can specify the weekend days with a parameter.

3. In order to be able to exclude holidays we need a table with holiday dates which we will LEFT JOIN with our primary data. In this example I am using data from excel file but Tableau only allows custom sql or '<=' join operator against excel if we use 'legacy connection'...

with such prepared dataset let's introduce holidays count: and finally the aggregated measure: The results: I have not tested this extensively so do let me know if you find an error.

EDIT: I realized that - due to the fact I used 8 as the value for 'none' weekend day in the parameters - the formulas would yield incorrect results if we used that value (8%7==1%7). So I modified it and republished. Also removed redundant start_of_week parameter from DATEPART function.

1 of 1 people found this helpful
1 2 Previous Next