
1. Re: How to calculate business minutes, excluding weekends, holidays and minutes out of range?
Austen Robinson May 9, 2018 1:28 PM (in response to Samuel Martins)Substitute your fields to suite.
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