Find Working Days Based on Weekly or Monthly Schedule

Function calcEndDate(start_date, weekday_duration, weekend_duration)

    ratio = weekday_duration / weekend_duration
    temp_date = start_date
    day_name = ""

    For i = 1 To weekday_duration
        day_name = Format(temp_date, "dddd")
        If (day_name = "Saturday" Or day_name = "Sunday") Then
            temp_date = temp_date + 1 / ratio
        Else
            temp_date = temp_date + 1
        End If
    Next
    calcEndDate = temp_date

End Function

    _count = 
    COUNTX (
        FILTER (
            VALUES('fact'[_factDate]),
            WEEKDAY ( 'fact'[_factDate], 1 ) <> 1
                && WEEKDAY ( 'fact'[_factDate], 1 ) <> 7
        ),
        'fact'[_factDate]
    )

Calendar_Date	Calendar_Year	Calendar_Month
1/1/2000	2000	1

Excel - Finding work days in a month between two dates

How about this:

=NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))

It might be wise to expand it with some error checking like so:

=IF(NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))<0,0,NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0))))

MySQL function to find the number of working days between two dates

This expression -

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

calculates the number of business days between the start date @S and the end date @E.

Assumes end date (@E) is not before start date (@S).
Compatible with DATEDIFF in that the same start date and end date
gives zero business days.
Ignores holidays.

The string of digits is constructed as follows. Create a table of
start days and end days, the rows must start with monday (WEEKDAY
0) and the columns must start with Monday as well. Fill in the
diagonal from top left to bottom right with all 0 (i.e. there are 0
working days between Monday and Monday, Tuesday and Tuesday, etc.).
For each day start at the diagonal (must always be 0) and fill in
the columns to the right, one day at a time. If you land on a
weekend day (non business day) column, the number of business days
doesn't change, it is carried from the left. Otherwise, the number
of business days increases by one. When you reach the end of the
row loop back to the start of the same row and continue until you
reach the diagonal again. Then go on to the next row.

E.g. Assuming Saturday and Sunday are not business days -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

Please let me know if you find any bugs.

-Edit
improved table:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

Calculate Number of Working Days based on a Month and Year Column - DAX

Although a Calendar table based approach is recommended as in the comment by RADO and Strawberryshrub, it is also possible to do this with DAX calculated column.

In the example below, I'm assuming MonthYear column contains the first day of each month.

WorkingDays = 
VAR Year = YEAR( [MonthYear] )
VAR Month = MONTH( [MonthYear] )
VAR DatesInMonth = GENERATESERIES( [MonthYear], DATE( Year, Month + 1, 1 ) - 1, 1 )
RETURN SUMX(
    DatesInMonth,
    IF( WEEKDAY( [Value] ) IN { 1, 7 }, 0, 1 )
)

How would I get the amount of weekdays in a month with LocalDate - Java

If desired, see optimized brute-force solution at the end

Here is a non-brute-force implementation to calculate week days (Mon-Fri) in a month.

It uses YearMonth instead of LocalDate, since the day-of-month value is meaningless to the calculation.

public static int weekDaysInMonth(YearMonth yearMonth) {
    int len = yearMonth.lengthOfMonth(); // 28-31, supporting leap year
    int dow = yearMonth.atDay(1).getDayOfWeek().getValue(); // 1=Mon, 7=Sun
    return (dow <= 5 ? Math.min(len - 8, 26 - dow) : Math.max(len + dow - 16, 20));
}

Here is an overload taking a LocalDate, so it's easy to call if that's what you have.

public static int weekDaysInMonth(LocalDate date) {
    return weekDaysInMonth(YearMonth.from(date));
}

Test

System.out.println(weekDaysInMonth(LocalDate.parse("2018-04-15"))); // April 15, 2018
System.out.println(weekDaysInMonth(YearMonth.of(2018, 5)));         // May 2018

Output

21
23

---

Explanation of Formula

The formula in the return statement was created by examining the expected return value for every combination of len (number of days in month, 28 - 31) and dow (day-of-week of first day of month, 1=Mon - 7=Sun):

   |  1   2   3   4   5    6   7
   | Mo  Tu  We  Th  Fr   Sa  Su
---+----------------------------
28 | 20  20  20  20  20   20  20
29 | 21  21  21  21  21   20  20
30 | 22  22  22  22  21   20  21
31 | 23  23  23  22  21   21  22

Explanation for dow <= 5 (Mon-Fri)

Initially there are len - 8 weekdays, i.e. we subtract the 4 weekends that always exist in a month.

As we get to Thursday and Friday, we need to cap that for the 1 or 2 weekend days we lose. If you look at the 31-day row, we cap it at 26 - dow, i.e. for Friday (dow=5) we cap at 21, and for Thursday (dow=4) we cap at 22. For Monday-Wednesday, we also cap, but cap is equal to or higher than initial calculation, so it doesn't matter.

Capping is done using min(xxx, cap) method, so we get:

min(len - 8, 26 - dow)

Explanation for dow >= 6 (Sat-Sun)

You can see a small triangle in the lower-right corner. If we extend that pattern, we get:

   |  4   5   6   7
---+---------------
28 | 16  17  18  19
29 | 17  18  19  20
30 | 18  19  20  21
31 | 19  20  21  22

As a formula, that is len + dow - 16.

Comparing that to original grid, numbers bottom out at 20, which is done using max(xxx, bottom) method, so we get:

max(len + dow - 16, 20)

Conclusion

Finally we combine the two using ternary conditional operator:

dow <= 5  ?  min(len - 8, 26 - dow)  :  max(len + dow - 16, 20)

Full Java statement is then:

return (dow <= 5 ? Math.min(len - 8, 26 - dow) : Math.max(len + dow - 16, 20));

---

Brute-Force Solution

If you prefer a brute-force solution, you can ease it by skipping the first 4 weeks that always exists in a month:

public static int weekDaysInMonth(LocalDate refDate) {
    LocalDate firstOfMonth = refDate.withDayOfMonth(1);
    LocalDate nextMonth = firstOfMonth.plusMonths(1);
    int days = 20;
    for (LocalDate date = firstOfMonth.plusDays(28); date.isBefore(nextMonth); date = date.plusDays(1))
        if (date.getDayOfWeek().getValue() <= 5) // 1=Mon - 5=Fri, i.e. not 6=Sat and 7=Sun
            days++;
    return days;
}

Using Python to count the number of business days in a month?

This is a long-winded way, but at least it works and doesn't require anything other than the standard modules.

import datetime

now = datetime.datetime.now()
holidays = {datetime.date(now.year, 8, 14)} # you can add more here
businessdays = 0
for i in range(1, 32):
    try:
        thisdate = datetime.date(now.year, now.month, i)
    except(ValueError):
        break
    if thisdate.weekday() < 5 and thisdate not in holidays: # Monday == 0, Sunday == 6 
        businessdays += 1

print businessdays

How to calculate Number of 'Working days' between two dates in Javascript using moment.js?

Just divide it into 3 parts.. first week, last week and the in between, something like this:

function workday_count(start,end) {
  var first = start.clone().endOf('week'); // end of first week
  var last = end.clone().startOf('week'); // start of last week
  var days = last.diff(first,'days') * 5 / 7; // this will always multiply of 7
  var wfirst = first.day() - start.day(); // check first week
  if(start.day() == 0) --wfirst; // -1 if start with sunday 
  var wlast = end.day() - last.day(); // check last week
  if(end.day() == 6) --wlast; // -1 if end with saturday
  return wfirst + Math.floor(days) + wlast; // get the total
} //              ^ EDIT: if days count less than 7 so no decimal point

The test code

var ftest = {date:'2015-02-0',start:1,end:7};
var ltest = {date:'2015-02-2',start:2,end:8};
var f = 'YYYY-MM-DD';
for(var z=ftest.start; z<=ftest.end; ++z) {
  var start = moment(ftest.date + z);
  for(var y=ltest.start; y<=ltest.end; ++y) {
    var end = moment(ltest.date + y);
    var wd = workday_count(start,end);
    console.log('from: '+start.format(f),'to: '+end.format(f),'is '+wd+' workday(s)');
  }
}

Output of test code:

from: 2015-02-01 to: 2015-02-22 is 15 workday(s)
from: 2015-02-01 to: 2015-02-23 is 16 workday(s)
from: 2015-02-01 to: 2015-02-24 is 17 workday(s)
from: 2015-02-01 to: 2015-02-25 is 18 workday(s)
from: 2015-02-01 to: 2015-02-26 is 19 workday(s)
from: 2015-02-01 to: 2015-02-27 is 20 workday(s)
from: 2015-02-01 to: 2015-02-28 is 20 workday(s)
from: 2015-02-02 to: 2015-02-22 is 15 workday(s)
from: 2015-02-02 to: 2015-02-23 is 16 workday(s)
from: 2015-02-02 to: 2015-02-24 is 17 workday(s)
from: 2015-02-02 to: 2015-02-25 is 18 workday(s)
from: 2015-02-02 to: 2015-02-26 is 19 workday(s)
from: 2015-02-02 to: 2015-02-27 is 20 workday(s)
from: 2015-02-02 to: 2015-02-28 is 20 workday(s)
from: 2015-02-03 to: 2015-02-22 is 14 workday(s)
from: 2015-02-03 to: 2015-02-23 is 15 workday(s)
from: 2015-02-03 to: 2015-02-24 is 16 workday(s)
from: 2015-02-03 to: 2015-02-25 is 17 workday(s)
from: 2015-02-03 to: 2015-02-26 is 18 workday(s)
from: 2015-02-03 to: 2015-02-27 is 19 workday(s)
from: 2015-02-03 to: 2015-02-28 is 19 workday(s)
from: 2015-02-04 to: 2015-02-22 is 13 workday(s)
from: 2015-02-04 to: 2015-02-23 is 14 workday(s)
from: 2015-02-04 to: 2015-02-24 is 15 workday(s)
from: 2015-02-04 to: 2015-02-25 is 16 workday(s)
from: 2015-02-04 to: 2015-02-26 is 17 workday(s)
from: 2015-02-04 to: 2015-02-27 is 18 workday(s)
from: 2015-02-04 to: 2015-02-28 is 18 workday(s)
from: 2015-02-05 to: 2015-02-22 is 12 workday(s)
from: 2015-02-05 to: 2015-02-23 is 13 workday(s)
from: 2015-02-05 to: 2015-02-24 is 14 workday(s)
from: 2015-02-05 to: 2015-02-25 is 15 workday(s)
from: 2015-02-05 to: 2015-02-26 is 16 workday(s)
from: 2015-02-05 to: 2015-02-27 is 17 workday(s)
from: 2015-02-05 to: 2015-02-28 is 17 workday(s)
from: 2015-02-06 to: 2015-02-22 is 11 workday(s)
from: 2015-02-06 to: 2015-02-23 is 12 workday(s)
from: 2015-02-06 to: 2015-02-24 is 13 workday(s)
from: 2015-02-06 to: 2015-02-25 is 14 workday(s)
from: 2015-02-06 to: 2015-02-26 is 15 workday(s)
from: 2015-02-06 to: 2015-02-27 is 16 workday(s)
from: 2015-02-06 to: 2015-02-28 is 16 workday(s)
from: 2015-02-07 to: 2015-02-22 is 10 workday(s)
from: 2015-02-07 to: 2015-02-23 is 11 workday(s)
from: 2015-02-07 to: 2015-02-24 is 12 workday(s)
from: 2015-02-07 to: 2015-02-25 is 13 workday(s)
from: 2015-02-07 to: 2015-02-26 is 14 workday(s)
from: 2015-02-07 to: 2015-02-27 is 15 workday(s)
from: 2015-02-07 to: 2015-02-28 is 15 workday(s)

---

Related Topics