Count Work Days Between Two Dates

Count work days between two dates

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

If you want to include holidays, you have to work it out a bit...

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)

Counting working days between two dates in data table using calendar table

Solution worked perfectly for me in POSTGRES:

table example
join
calendar table ON tsrange(task_assigned_date, task_got_to_work_date)&&tsrange(calendar.start_time, calendar.end_time)

Calculate business days

Here's a function from the user comments on the date() function page in the PHP manual. It's an improvement of an earlier function in the comments that adds support for leap years.

Enter the starting and ending dates, along with an array of any holidays that might be in between, and it returns the working days as an integer:

<?php
//The function returns the no. of business days between two dates and it skips the holidays
function getWorkingDays($startDate,$endDate,$holidays){
    // do strtotime calculations just once
    $endDate = strtotime($endDate);
    $startDate = strtotime($startDate);


    //The total number of days between the two dates. We compute the no. of seconds and divide it to 60*60*24
    //We add one to inlude both dates in the interval.
    $days = ($endDate - $startDate) / 86400 + 1;

    $no_full_weeks = floor($days / 7);
    $no_remaining_days = fmod($days, 7);

    //It will return 1 if it's Monday,.. ,7 for Sunday
    $the_first_day_of_week = date("N", $startDate);
    $the_last_day_of_week = date("N", $endDate);

    //---->The two can be equal in leap years when february has 29 days, the equal sign is added here
    //In the first case the whole interval is within a week, in the second case the interval falls in two weeks.
    if ($the_first_day_of_week <= $the_last_day_of_week) {
        if ($the_first_day_of_week <= 6 && 6 <= $the_last_day_of_week) $no_remaining_days--;
        if ($the_first_day_of_week <= 7 && 7 <= $the_last_day_of_week) $no_remaining_days--;
    }
    else {
        // (edit by Tokes to fix an edge case where the start day was a Sunday
        // and the end day was NOT a Saturday)

        // the day of the week for start is later than the day of the week for end
        if ($the_first_day_of_week == 7) {
            // if the start date is a Sunday, then we definitely subtract 1 day
            $no_remaining_days--;

            if ($the_last_day_of_week == 6) {
                // if the end date is a Saturday, then we subtract another day
                $no_remaining_days--;
            }
        }
        else {
            // the start date was a Saturday (or earlier), and the end date was (Mon..Fri)
            // so we skip an entire weekend and subtract 2 days
            $no_remaining_days -= 2;
        }
    }

    //The no. of business days is: (number of weeks between the two dates) * (5 working days) + the remainder
//---->february in none leap years gave a remainder of 0 but still calculated weekends between first and last day, this is one way to fix it
   $workingDays = $no_full_weeks * 5;
    if ($no_remaining_days > 0 )
    {
      $workingDays += $no_remaining_days;
    }

    //We subtract the holidays
    foreach($holidays as $holiday){
        $time_stamp=strtotime($holiday);
        //If the holiday doesn't fall in weekend
        if ($startDate <= $time_stamp && $time_stamp <= $endDate && date("N",$time_stamp) != 6 && date("N",$time_stamp) != 7)
            $workingDays--;
    }

    return $workingDays;
}

//Example:

$holidays=array("2008-12-25","2008-12-26","2009-01-01");

echo getWorkingDays("2008-12-22","2009-01-02",$holidays)
// => will return 7
?>

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)

How can I calculate the number of working days between two dates

May be this code snippet will help:

<?php
//get current month for example
$beginday = date("Y-m-01");
$lastday  = date("Y-m-t");

$nr_work_days = getWorkingDays($beginday, $lastday);
echo $nr_work_days;

function getWorkingDays($startDate, $endDate)
{
    $begin = strtotime($startDate);
    $end   = strtotime($endDate);
    if ($begin > $end) {
        echo "startdate is in the future! <br />";

        return 0;
    } else {
        $no_days  = 0;
        $weekends = 0;
        while ($begin <= $end) {
            $no_days++; // no of days in the given interval
            $what_day = date("N", $begin);
            if ($what_day > 5) { // 6 and 7 are weekend days
                $weekends++;
            };
            $begin += 86400; // +1 day
        };
        $working_days = $no_days - $weekends;

        return $working_days;
    }
}

Another solution can be:
Get date range between two dates excluding weekends

Calculate working days between two dates in Javascript excepts holidays

The easiest way to achieve it is looking for these days between your begin and end date.

Edit: I added an additional verification to make sure that only working days from holidays array are subtracted.

$(document).ready(() => {
  $('#calc').click(() => {
  var d1 = $('#d1').val();
  var d2 = $('#d2').val();
    $('#dif').text(workingDaysBetweenDates(d1,d2));
  });
});

let workingDaysBetweenDates = (d0, d1) => {
  /* Two working days and an sunday (not working day) */
  var holidays = ['2016-05-03', '2016-05-05', '2016-05-07'];
  var startDate = parseDate(d0);
  var endDate = parseDate(d1);  

// Validate input
  if (endDate <= startDate) {
    return 0;
  }

// Calculate days between dates
  var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
  startDate.setHours(0, 0, 0, 1);  // Start just after midnight
  endDate.setHours(23, 59, 59, 999);  // End just before midnight
  var diff = endDate - startDate;  // Milliseconds between datetime objects    
  var days = Math.ceil(diff / millisecondsPerDay);

  // Subtract two weekend days for every week in between
  var weeks = Math.floor(days / 7);
  days -= weeks * 2;

  // Handle special cases
  var startDay = startDate.getDay();
  var endDay = endDate.getDay();
    
  // Remove weekend not previously removed.   
  if (startDay - endDay > 1) {
    days -= 2;
  }
  // Remove start day if span starts on Sunday but ends before Saturday
  if (startDay == 0 && endDay != 6) {
    days--;  
  }
  // Remove end day if span ends on Saturday but starts after Sunday
  if (endDay == 6 && startDay != 0) {
    days--;
  }
  /* Here is the code */
  holidays.forEach(day => {
    if ((day >= d0) && (day <= d1)) {
      /* If it is not saturday (6) or sunday (0), substract it */
      if ((parseDate(day).getDay() % 6) != 0) {
        days--;
      }
    }
  });
  return days;
}
           
function parseDate(input) {
    // Transform date from text to date
  var parts = input.match(/(\d+)/g);
  // new Date(year, month [, date [, hours[, minutes[, seconds[, ms]]]]])
  return new Date(parts[0], parts[1]-1, parts[2]); // months are 0-based
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<input type="text" id="d1" value="2016-05-02"><br>
<input type="text" id="d2" value="2016-05-08">

<p>Working days count: <span id="dif"></span></p>
<button id="calc">Calc</button>

<p>
Now it shows 5 days, but I need for example add holidays 
3 and 5 May (2016-05-03 and 2016-05-05) so the result will be 3 working days
</p>

    /// <summary>
    /// Calculates number of business days, taking into account:
    ///  - weekends (Saturdays and Sundays)
    ///  - bank holidays in the middle of the week
    /// </summary>
    /// <param name="firstDay">First day in the time interval</param>
    /// <param name="lastDay">Last day in the time interval</param>
    /// <param name="bankHolidays">List of bank holidays excluding weekends</param>
    /// <returns>Number of business days during the 'span'</returns>
    public static int BusinessDaysUntil(this DateTime firstDay, DateTime lastDay, params DateTime[] bankHolidays)
    {
        firstDay = firstDay.Date;
        lastDay = lastDay.Date;
        if (firstDay > lastDay)
            throw new ArgumentException("Incorrect last day " + lastDay);

        TimeSpan span = lastDay - firstDay;
        int businessDays = span.Days + 1;
        int fullWeekCount = businessDays / 7;
        // find out if there are weekends during the time exceedng the full weeks
        if (businessDays > fullWeekCount*7)
        {
            // we are here to find out if there is a 1-day or 2-days weekend
            // in the time interval remaining after subtracting the complete weeks
            int firstDayOfWeek = (int) firstDay.DayOfWeek;
            int lastDayOfWeek = (int) lastDay.DayOfWeek;
            if (lastDayOfWeek < firstDayOfWeek)
                lastDayOfWeek += 7;
            if (firstDayOfWeek <= 6)
            {
                if (lastDayOfWeek >= 7)// Both Saturday and Sunday are in the remaining time interval
                    businessDays -= 2;
                else if (lastDayOfWeek >= 6)// Only Saturday is in the remaining time interval
                    businessDays -= 1;
            }
            else if (firstDayOfWeek <= 7 && lastDayOfWeek >= 7)// Only Sunday is in the remaining time interval
                businessDays -= 1;
        }

        // subtract the weekends during the full weeks in the interval
        businessDays -= fullWeekCount + fullWeekCount;

        // subtract the number of bank holidays during the time interval
        foreach (DateTime bankHoliday in bankHolidays)
        {
            DateTime bh = bankHoliday.Date;
            if (firstDay <= bh && bh <= lastDay)
                --businessDays;
        }

        return businessDays;
    }

int firstDayOfWeek = firstDay.DayOfWeek == DayOfWeek.Sunday 
    ? 7 : (int)firstDay.DayOfWeek;
int lastDayOfWeek = lastDay.DayOfWeek == DayOfWeek.Sunday
    ? 7 : (int)lastDay.DayOfWeek;