1 2 Previous Next 27 Replies Latest reply on Jul 28, 2016 12:42 AM by Łukasz Majewski Go to original post
• ###### 15. Re: Calculate time difference with certain time range

Ok. Got it.. I will edit it for other datasource using dateparse fuction. I will get back with the updated calc. Thanks for the feedback.

• ###### 16. Re: Calculate time difference with certain time range

I did it with your calculation and I am not sure is there any typical mistake?

if datepart('hour',[End])>11 (It should be 12 right?)

then min(1,([End]-(date([End])+12/24) )*24) else 0 END

By the way, I am now trying to count the working time by following approaches:

1. if weekday = Mon to Fri then use your formula

2. if weekday = Saturday then count working time 09:00 - 13:00 (no need to count lunch hour)

3. if weekday = Sunday then ignored in calculation

For example:

Start: 16/7/2016 (Sat) 10:00

End:   18/7/2016 (Mon) 18:00

Calculation:

Sat: 1300-1000 = 3 hrs

Sun: ignored

Mon: 1700 - 0900 - lunch hr = 7 hrs

Ans : 3 + 7 = 10 hrs

I still cannot solve this problem after spending a week. Can anyone give me some ideas about it?

Thank you very much.

• ###### 17. Re: Calculate time difference with certain time range

After trying the work days calculation, I am facing a difficulty to combine both calculations together.

Meanwhile, I have prepared a external excel file of the public holidays that helping me to define actual working hour of the staff.

Is that possible to do this calculation in Tableau 9.0?

• ###### 18. Re: Calculate time difference with certain time range

If you share a sample data - twbx or xlsx - I will implement a solution you seek.

• ###### 19. Re: Calculate time difference with certain time range

There are 2 sheets: 1. Event   2. Public holiday

In "Public holiday" sheet,

Business day = Mon to Fri and not a holiday

Sat workday = Sat and not a holiday

I would like to use the approaches that mentioned before to count the date difference of "Event" sheet.

And use "Public holiday" sheet to exclude Sunday & Holiday in calculation.

Thank you very much.

• ###### 20. Re: Calculate time difference with certain time range

You provided 2 records and a list of all days in a year with a holiday indicator.

None of the holidays falls within the 2 records' dates.

I will not use this sample...

• ###### 21. Re: Calculate time difference with certain time range

Sorry for the inappropriate sample.

I have updated 2 events in this sample and I would like to have the result below:

Working time

Mon to Fri: 09:00 to 17:00 (exclude lunch time 1200 to 1300)

Sat: 09:00 to 13:00

Event AAA:

22/7/2016 (Fri) (1700-1300)=4 hrs

23/7/2016 (Sat) (1300-0900)=4 hrs

25/7/2016 (Mon) (1000-0900)=1 hrs

AAA=9 hrs

Event BBB:

30/6/2016 (Thu) (1700-1300)=4 hrs (1200-1300 lunch time)

1/7/2016 (Fri) (Holiday do not count)

2/7/2016 (Sat) (1300-1000)=3 hrs

BBB=7 hrs

• ###### 22. Re: Calculate time difference with certain time range

alright...

• I used my sample dataset with 1 holiday
• data source is  ms access db as my desktop 9.0-9.3 could not establish odbc conection to excel which is required for a custom left join
• the method is exactly the same as the referenced thread detailed; I only simplified the duplicated calculation of [Lunch Time] since weekend days are not relevant here (?)
• should you need further explanation please refer to Re: How to Calculate Working Minutes- Excluding Weekends and Holidays

here is the result:

9.3 wb attached

cheers

Łukasz

1 of 1 people found this helpful
• ###### 23. Re: Calculate time difference with certain time range

Thanks, this is exactly what I was looking for.

I found that all parameters' data type are integer. How can I calculate the difference between 0930 to 1730 instead of 0900 to 1700?

On the other hand, I am using Tableau Server Datasource that cannot join with other datasource. Can data blending do the same things?

• ###### 24. Re: Calculate time difference with certain time range

Yes, that is how I decided to store HH:mm in parameters. To change it just type 930 & 1730:

As to blending - no it will not work and only left join with <=,  >= operators are applicable to this method. The objective is to end up with holiday dates along your start & end dates:

so you got to incorporate it somehow.

2 of 2 people found this helpful
• ###### 25. Re: Calculate time difference with certain time range

I tried to set the parameters like this:

Then the Work Time & Lunch Time results became strange:

I have no idea why would this happen since I am still studying your formula

• ###### 26. Re: Calculate time difference with certain time range

Problem solved.

For example,

Change

(INT([satend]/100)*3600+[satend]%100-INT([satstart]/100)*3600+[satstart]%100)

to

(INT([satend]/100)*3600+[satend]%100*60-(INT([satstart]/100)*3600+[satstart]%100*60))

Result:

• ###### 27. Re: Calculate time difference with certain time range

You're right! I never tested this thoroughly and missed that %100 yields minutes and not seconds...

Will need to republish corrected version when I have some time

Thanks for spotting this.

Łukasz

1 2 Previous Next