6 Replies Latest reply on Apr 22, 2014 8:19 AM by Prashant Sharma

Weekend Calculation ** Urgent**

Hi Experts,

I am building a metrics on cycle time, which is focus on total time taken to close an SR.

below is the example:

Start DateEnd DateExpected result
3/29/2014  10:52:00 AM4/3/2014  9:32:00 AMapprx 75 hrs
3/27/2014  9:44:00 AM4/4/2014  9:15:00 AMapprx 138 hrs

Assumption :

24 hrs support,

Cut off time on Friday mid-night 12 and re start the que monitoring on Monday 6.00 AM

Regards,

Abhilash..

• 1. Re: Weekend Calculation ** Urgent**

Use the DateDiff function.

• 2. Re: Weekend Calculation ** Urgent**

Hi Russell,

Datediff function gives him total hours using formula - datediff('hour',[Start Date],[End Date])

But here he wants to subtract the time from Friday night to Monday morning 6 am & for that I think he needs to create a master date & with the help of right join on start date & end date he will be able to subtract the respective time. Use following join while creating joins in Tableau.

Start Date <= Master Date

End Date >= Master Date

FYI - When creating a master date you have to consider datetime both in this scenario.

These are my thoughts. Let me know if there is any simple method or trick.

Warm Regards,

Prashant Sharma - India | LinkedIn

• 3. Re: Weekend Calculation ** Urgent**

Hi Prashant,

can you help me to create a master date or can you put an example for me that would be really helpful.

Regards,

Abhilash..

• 4. Re: Weekend Calculation ** Urgent**

I think there is another way to do this

First you have to know how many weeks separate the end date and start date.

For that we'll need the week number of the end date and start date. If they are different, we should have a value of more than one. And if we have that, that means we have two days that are on a weekend.

So this gives the following formula

2*DATEPART('week',[EndDate])-DATEPART('week',[StartDate])

Since a day is 24 hours,that means 48 hours and since your week starts on Monday at 6, then add 6 to that to get 54 hours that are to be deducted for each weekend.

Giving the following formula:

```DATEDIFF('hour',[StartDate],[EndDate]) -(54*(DATEPART('week',[EndDate])-DATEPART('week',[StartDate])))

```

Does that work?

• 5. Re: Weekend Calculation ** Urgent**

Hi Abhilash,

I have tried Master Date method but then I have tried to done this using datediff as Russel suggest. Following is only example & you have to change calculated field according to your requirement. I will also try it according to your requirement when I will get time but try to use the below formula -

((DATEDIFF('day', [Start Date], [End Date]) + 1)

-(DATEDIFF('week', [Start Date], [End Date]) * 2)

-(IF DATENAME('weekday', [Start Date]) = 'Sunday' or DATENAME('weekday', [Start Date]) = 'Saturday' THEN 1 ELSE 0 END)

-(IF DATENAME('weekday', [End Date]) = 'Sunday' or DATENAME('weekday', [End Date]) = 'Saturday' THEN 1 ELSE 0 END))*24

Warm Regards,

Prashant Sharma - India | LinkedIn

• 6. Re: Weekend Calculation ** Urgent**

Hey Edgar,

Your method is worked like charm. Interesting approach. Thanks for sharing. Its a learning question for me. Happy Learning !

Warm Regards,

Prashant Sharma - India | LinkedIn