Use the DateDiff function.
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.
can you help me to create a master date or can you put an example for me that would be really helpful.
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
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:
Does that work?
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