1 2 Previous Next 18 Replies Latest reply on Mar 26, 2018 5:29 AM by Jan Brooijmans

Date Differnce - only working hours - only working days

I am stuck in issue i need help
I want to count hours difference between two dates on two cases
1) count hours for only working hours (8am-5:30 pm)

2) exclude weekdays (Saturday, Sunday )

For example if an order is placed at 10/2/2013 5:28 p.m. on Friday and filled on 10/5/2013 8:10 a.m. on Monday.

Then time difference will be 12 minutes.

Jive Monitoring

• 1. Re: Date Differnce - only working hours - only working days

its like this Alex Kerin

• 2. Re: Date Differnce - only working hours - only working days

Nobody said this is going to be easy, so you have been warned.

Tableau doesn't have any built-in logic for these kind of calculations, so it had to be hacked. Use "formulae" below and replace field names.

Option 1: Expressing number of working days between two dates as a fraction (decimal) of 9.5-hour working day, between 08:00 and 17:30.

```//work days on the day of Start Date
IF 1 < DATEPART('weekday',[Start Date]) AND DATEPART('weekday',[Start Date]) < 7 //only for weekdays
AND ([Start Date] - DATETRUNC('day',[Start Date])) < 1/24*17.5  //and only for events starting before work end
AND DATETRUNC('day',[Start Date]) < DATETRUNC('day',[End Date]) //ignore same-day events, they are counted with End Date
THEN
//work days elapsed since Start Date (or 08:00, whchever is later) to work end time (17:30), as fraction of 9.5 hour work day
((DATETRUNC('day',[Start Date]) + 1/24*17.5) - MAX([Start Date],DATETRUNC('day',[Start Date]) + 1/24*8))/(1/24*(17.5-8))
ELSE 0 //zero for weekends
END
+
//number of *whole* (24 hours) work days between two dates
//working days from Start Date to the end of that week
IF DATEPART('weekday',[Start Date]) == 7 //0 for Sat
//0 for start and end on the same week, to avoid double-counting,
//these days will be counted with End Date
OR DATETRUNC('week',[Start Date]) == DATETRUNC('week',[End Date])
THEN 0
ELSE 7 - DATEPART('weekday',[Start Date]) - 1
END
+
//work days from whole weeks between
CASE SIGN(DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))
WHEN -1 THEN 0 //set to 0 if whole weeks between is negative
ELSE INT((DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))/7)*5
END
+
//working days from beggining of that week to the End Date
IF DATEPART('weekday',[End Date]) == 1 OR DATETRUNC('day',[Start Date]) == DATETRUNC('day',[End Date])
THEN 0 //set 0 for Sundays and same-day start/end
//number of days
//from: either Monday, or Start Date if it is later than Monday
//to: either End Date, or Saturday (excluding) whichever is earlier (toexclude Sat)
ELSE INT(MIN(DATETRUNC('day',[End Date]),DATETRUNC('week',[End Date])+6) - MAX(DATETRUNC('week',[End Date])+1,DATETRUNC('day',[Start Date])+1))
END
+
//work days on the day of End Date
IF 1 < DATEPART('weekday',[End Date]) AND DATEPART('weekday',[End Date]) < 7 //only for weekdays
AND ([End Date] - DATETRUNC('day',[End Date])) > 1/24*8 THEN //and only for events ending after work start
//work days elapsed from 8:00 until Start Date (or 17:30, whchever is earlier), as fraction of 9.5 hour work day
(MIN([End Date],DATETRUNC('day',[End Date]) + 1/24*17.5) - (DATETRUNC('day',[End Date]) + 1/24*8)) /(1/24*(17.5-8))
ELSE 0 //zero for weekends
END
```

Option 2: Expressing the same in your normal days, hours and minutes, i.e. 2:04:12 - two days, four hours and twelve minutes, where days are 9.5-hour working days, not 24 hour days. So 2:04:12 will convert to 19+4 hours and 12 minutes.

```//work days on the day of Start Date
IF 1 < DATEPART('weekday',[Start Date]) AND DATEPART('weekday',[Start Date]) < 7 //only for weekdays
AND ([Start Date] - DATETRUNC('day',[Start Date])) < 1/24*17.5  //and only for events starting before work end
AND DATETRUNC('day',[Start Date]) < DATETRUNC('day',[End Date]) //ignore same-day events, they are counted with End Date
THEN
//work days elapsed since Start Date (or 08:00, whchever is later) to work end time (17:30)
((DATETRUNC('day',[Start Date]) + 1/24*17.5) - MAX([Start Date],DATETRUNC('day',[Start Date]) + 1/24*8))
ELSE 0 //zero for weekends
END
+
//number of *whole* (24 hours) work days between two dates
//working days from Start Date to the end of that week
IF DATEPART('weekday',[Start Date]) == 7 //0 for Sat
//0 for start and end on the same week, to avoid double-counting,
//these days will be counted with End Date
OR DATETRUNC('week',[Start Date]) == DATETRUNC('week',[End Date])
THEN 0
ELSE 7 - DATEPART('weekday',[Start Date]) - 1
END
+
//work days from whole weeks between
CASE SIGN(DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))
WHEN -1 THEN 0 //set to 0 if whole weeks between is negative
ELSE INT((DATETRUNC('week',[End Date]) - (DATETRUNC('week',[Start Date])+7))/7)*5
END
+
//working days from beggining of that week to the End Date
IF DATEPART('weekday',[End Date]) == 1 OR DATETRUNC('day',[Start Date]) == DATETRUNC('day',[End Date])
THEN 0 //set 0 for Sundays and same-day start/end
//number of days
//from: either Monday, or Start Date if it is later than Monday
//to: either End Date, or Saturday (excluding) whichever is earlier (toexclude Sat)
ELSE INT(MIN(DATETRUNC('day',[End Date]),DATETRUNC('week',[End Date])+6) - MAX(DATETRUNC('week',[End Date])+1,DATETRUNC('day',[Start Date])+1))
END
+
//work days on the day of End Date
IF 1 < DATEPART('weekday',[End Date]) AND DATEPART('weekday',[End Date]) < 7 //only for weekdays
AND ([End Date] - DATETRUNC('day',[End Date])) > 1/24*8 THEN //and only for events ending after work start
//work days elapsed from 8:00 until Start Date (or 17:30, whchever is earlier)
(MIN([End Date],DATETRUNC('day',[End Date]) + 1/24*17.5) - (DATETRUNC('day',[End Date]) + 1/24*8))
ELSE 0 //zero for weekends
END
+ 1 //add one day to make Tableau's d:hh:mm display correctly
```

Enjoy

• 3. Re: Date Differnce - only working hours - only working days

This is amazing, Dimitri!!

• 4. Re: Date Differnce - only working hours - only working days

Amazing is correct.  The fact that people take the time to answer (seemingly, to me, anyway) complex questions like this is awesome.

• 5. Re: Date Differnce - only working hours - only working days

Agreed - nice

• 6. Re: Date Differnce - only working hours - only working days

Great Thanks Dimitri.......

• 7. Re: Date Differnce - only working hours - only working days

Here are some cases , I am taking difference in hours

Dimitri Blyumin

• 8. Re: Date Differnce - only working hours - only working days

I had a look at the samples, but I don't understand what the question is.

• 9. Re: Date Differnce - only working hours - only working days

actually here is problem
if it is same day and not Saturday/Sunday then count hours between same date with no condition
like :  start date                  end date                          hour  difference

fri 2013-01-01 4:30 pm        fri 2013-01-01 8:30 pm        4 hours

if it is not for same days then count only working hour difference , working day (8am to 5.30 pm)

like :  start date                  end date                            hour  difference

thu 2013-01-01 4:30 pm       fri 2013-01-01 12:30 pm               5 hour 30 minutes
only difference for working hours

third case is like if end date comes in weekend then simply ignore weekend and count only working hours of working day

 like :  start date end date                           hour  difference

fri 2013-01-01 4:30 pm        Sat 2013-01-01 8:30 pm         1 hours

(only work hour from 4:30 to 5:30 one hour , ignore weekend)

yes dimitri i am in this tough scenario Dimitri Blyumin

• 10. Re: Date Differnce - only working hours - only working days

Holy sh--! D that's some fancy dancin'.

--Shawn

• 11. Re: Date Differnce - only working hours - only working days

Dimitri Blyumin  I found this very useful, but would like to expand it and create another view with Holidays excluded.  Any ideas?  I am currently using networkdays in excel to do the same.

I have researched this network days thread, but it appears your script already covers all scenarios except holidays and I have a separate data file with holiday dates.

Thanks....

• 12. Re: Date Differnce - only working hours - only working days

One way to do it would be to hard-code the public holiday dates right into the calculation. Another way is to have a reference table with all the holiday dates and do a data blend (your mileage may vary, data blends impose certain constraints the contents of the sheet). Yet another is to incorporate such reference table in the original source and join it to have an extra column with a flag marking holiday records.

• 13. Re: Date Differnce - only working hours - only working days

WHOA - this solution saved me a lot of time - thanks for that.

Dimitri Blyumin: I am struggling right now with holidays though could You please give me some tips how to incorporate the 'hard coded holidays' approach to Your solution?

• 14. Re: Re: Date Differnce - only working hours - only working days

You can add a snippet of code for each date of public holidays before or after the main block. It will look something like this:

```IF [Start Date] <= [your holiday date 1] AND [your holiday date 1] <= [End Date]
THEN -1 //deduct one day (or fraction of a day, as required)
ELSE 0 // nothing to deduct or add
END
+
IF [Start Date] <= [your holiday date 2] AND [your holiday date 2] <= [End Date]
THEN -1 //deduct one day (or fraction of a day, as required)
ELSE 0 // nothing to deduct or add
END
//...etc.
```

The potential problem would be if a public holiday falls on a weekend, in which case the code will overcompensate for it, but this can be avoided by checking for that and not including such occasions.

1 2 Previous Next