1 Reply Latest reply on May 9, 2018 1:28 PM by Austen Robinson

# How to calculate business minutes, excluding weekends, holidays and minutes out of range?

Hello Tableau Community,

I need calculate the difference between two dates. But i have some rules: I can include in my calculation only the minutes that i work per day, that would be 480 minutes. The minutes remaning (before and after) in the business day, and of course weekends and holidays i have to eliminate. I already check a lot of solutions as: "how to eliminate weekends and holidays ?" or "how to calculate business days ?", it was good, but i could not solve my problem 100%. My date field format is: dd/mm/yyyy  hh:mm:ss. Here is my package workbook and excel file attached.

FYI: Tableau version is 10.4

• ###### 1. Re: How to calculate business minutes, excluding weekends, holidays and minutes out of range?

WeekdayStart

// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

DatePart('weekday',[Start Date])

WeekdayEnd

// Return day number in week (1=Sunday, 2=Monday, etc..., 7=Saturday)

DatePart('weekday',[End Date])

Work Days

// (1) Calculate number of whole weeks between dates, then convert to # of Work Days

INT(([End Date]-[Start Date])/7)*5

+

// (2) If sum of the first and last week days is a full week, then it was accounted for above,

//     so we need to subtract those work days because we will calculate work days for the

//     first and last weeks in the next two steps

IF

// # of total days in 1st Week

8-[WeekdayStart]

+

// # of total days in Last Week

[WeekdayEnd]

> 7

// Subtract week of work days from step (1) which will be calculated in steps (3) & (4) below

THEN -5

ELSE 0

END

+

// (3) Add # of Work Days for 1st Week

// If 7 Days, then subtract 2 for Sun & Sat

IF 8-[WeekdayStart] = 7 THEN 8-[WeekdayStart]-2

// If 1 Day and it's Sunday, then exclude

ELSEIF 8-[WeekdayStart] = 1 AND [WeekdayStart] = 1 THEN 0

// Otherwise subtract the one Sunday

ELSE 8-[WeekdayStart]-1

END

+

// (4) Add # of Work Days for Last Week

// If 7 Days, then subtract 2 for Sun & Sat

IF [WeekdayEnd] = 7 THEN [WeekdayEnd]-2

// If 1 Day and it's Sunday, then exclude

ELSEIF [WeekdayEnd] = 1 THEN 0

// Otherwise subtract the one Sunday

ELSE [WeekdayEnd]-1

END