1 2 Previous Next 27 Replies Latest reply on Jul 28, 2016 12:42 AM by Łukasz Majewski

# Calculate time difference with certain time range

Hi all,

I have this data and want to calculate the time difference between two dates.

 Event Starting date Starting time End date End Time A 14/7/2016 8:00 15/7/2016 14:30

During starting time and end time, i would like to exclude non working hours:

i.e. just count 9:00 to 17:00

therefore the result of this example is:

14/7/2016 9:00 to 17:00 = 8 hrs

15/7/2016 9:00 to 14:30 = 5.5 hrs

How to get 13.5 hrs as the result?

Thank you

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

Here you go.

Need to breakdown the range into 5 parts and sum up.

[Start time 1st day]

date([Start])+9/24

[end Time 1st day]

date([Start])+17/24

[Start time last day]

date([End])+9/24

[end Time last day]

date([End])+17/24

[days diff]

datediff('day',[end Time 1st day],[Start time last day])-1

[calc hours]

(max(0,[end Time 1st day]-max([Start time 1st day],[Start])))*24

+(max(0,min([end Time last day],[End])-[Start time last day]))*24

+[days diff]*8

Thanks,

Shin

9.3 attached.

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

Hi Himhim,

Please find the attached screenshots to confirm the output. It is a two step process.

First you need to concatenate the date and time using below code.

Second please create a calculated field as difference is as below as shown below. Please use below code to achieve the same.

if DAY([Starting date])<>DAY([End date]) then

(IF DATEPART('hour',[Starting date]) - 9 = 0 THEN (DATEDIFF('minute',[Starting date],[End date])/60) - ((DAY([End date])-DAY([Starting date]))*16)

ELSE (DATEDIFF('minute',[Starting date],[End date])/60) - ((DAY([End date])-DAY([Starting date]))*16)-( 9 - DATEPART('hour',[Starting date]) ) END)

ELSE (DATEDIFF('minute',[Starting date],[End date])/60)

END

You can refer the same on below link.

http://kb.tableau.com/articles/knowledgebase/combining-date-and-time-single-field

Let me know if this helps. I am facing net issues. I will upload the workbook in next post.

Thanks and Regards,

Ashish Chaudhari

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

Please find the attached file. Let me know if this helps you in any way.

-Ashish

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

Brilliant logic. Nicely broken down in smaller parts.

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

Hi Himhim,

I got here the one that you are looking for, I manage to get the diff of the dates you provided.

Let me explain this to you, it is divided in 4 different parts:

1. Your Date String 1 = "07-14-2016" and Date String 2 = "07-14-2016"

2. Your Time 1 = "09:00" and Time 2 = "17:00"

3. I did a calculated field for the combination of both named "Combine 1" and "Combine 2" (see calculation in the workbook) after that, I change the data type to "Date and Time"

4. Lastly, we get the diff of them, DATEDIFF('hour',[Combine 1],[Combine 2]) , this time we will measure the HOUR as their diff.

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

Thanks Shinichiro, Ashish and Lester.

I really appreciated all the hard work you guys have done.

Date time calculation is quite difficult for me that i have to spend more time to think about the logic.

If i want to add lunch time (12:00 - 13:00) and exclude it in the calculation, can i using the same method to do it?

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

You can, but still you need to put maybe a catch where if the hour goes by 12:00 - 13:00

it should do a -1 on the total hours computed. Maybe you can attached a sample data for us

so we can help you out using the data that you provided.

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

Hi Himhim,

I am figuring this out.

Thanks and Regards,

Ashish Chaudhari

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

this formula seems to work although not sure if in all cases:

and the results:

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

Lukasz already replied, but just FYI for mine.

[calc hours exclude Lunch]

(max(0,[end Time 1st day]-max([Start time 1st day],[Start])))*24

+(max(0,min([end Time last day],[End])-[Start time last day]))*24

+[days diff]*(8-1)

-

if datepart('hour',[Start])<13

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

-

if datepart('hour',[End])>11

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

Thanks,

Shin

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

Hi Himhim,

Please find the calculated field for the exclusion of lunch time. Just copy and replace this one in the workbook to get going. (Calc_Differece)

If DAY([Starting date])<>DAY([End date]) then

(IF DATEPART('hour',[Starting date]) - 9 >= 0  and (DATEDIFF('minute',MAKEDATETIME(DATE([End date]),#09:00:00#),[End date])/60)<=4 and (DATEDIFF('minute',MAKEDATETIME(DATE([End date]),#09:00:00#),[End date])/60)>=3 THEN (DATEDIFF('minute',[Starting date],[End date])/60) - ((DAY([End date])-DAY([Starting date]))*16)

ELSE (DATEDIFF('minute',[Starting date],[End date])/60) - ((DAY([End date])-DAY([Starting date]))*16)-( 9 - DATEPART('hour',[Starting date]) ) - ((DAY([End date])-DAY([Starting date]))) END)

ELSEIF DATEPART('hour',[Starting date]) - 9 >= 0 and DAY([Starting date])=DAY([End date]) and (DATEDIFF('minute',[Starting date],[End date])/60)<=4 and (DATEDIFF('minute',[Starting date],[End date])/60)>=3 then (DATEDIFF('minute',[Starting date],[End date])/60)-1

ELSE (DATEDIFF('minute',[Starting date],[End date])/60)

END

Please refer to the below screenshot.

Let me know if this helps.

Thanks and Regards,

Ashish Chaudhari

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

Hi,

Here is the calculation:

(INT([End date]-[Starting date])-1)*8+min(max(0,[End Time]-9/24)*24,8)+min(max(0,17/24-[Starting time])*24,8)

Cheers,

Ray

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

Thank you for you guys' effort. It is really useful for me.

I will study all posted calculations today.

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

Thanks Ashish, i can use the calculation with excel data source.

However, I cannot adopt MAKEDATETIME function in my file.

Current data source is connecting to Tableau server and i am using Tableau desktop 9.0

1 2 Previous Next