Calculate the Number of Business Days Between Two Dates

$(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>

How to calculate number of business days (Monday to friday) between two dates in hive?

Simple question but complex answer :) Please note, i have chosen sat and sun as non business day.

First of all you need to generate a time series. Then count no of weekdays in the series.
Step 1 - Creating time series can be created using lateral view posexplode.

lateral view posexplode(split(space(datediff(t.end_dt,t.start_dt)),' ')) pe as i,x 

Step 2 - Calculate count if day is neither sat nor sun using extract(days of week timestamp_col) function.

Sunday - 1
Saturday - 7

case when extract(dayofweek from day) in (1,7) then 0 else 1 end

So, complete code will look like below -

select end_date, start_date, 
sum(case when extract(dayofweek from day) in (1,7) or or to_date(start_date )=to_date(end_date) then 0 else 1 end) no_of_week_day
from (
select  end_date, start_date , date_add (t.start_date,pe.i)   as Day
from    etl.x t
        lateral view posexplode(split(space(datediff(t.end_date,t.start_date)),' ')) pe as i,x )  rs
group by end_date, start_date

I ran above SQL for sample data.

EDIT : I updated the SQL with OR clause.

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 number of business days between two dates

Here's an answer implemented in Java 8 using java.time.*.

public class TestSo47314277 {

  /**
   * A set of federal holidays. Compared to iteration, using a
   * hash-based container provides a faster access for reading
   * element via hash code. Using {@link Set} avoids duplicates.
   * <p>
   * Add more dates if needed.
   */
  private static final Set<LocalDate> HOLIDAYS;

  static {
    List<LocalDate> dates = Arrays.asList(
        LocalDate.of(2017, 1, 2),
        LocalDate.of(2017, 1, 16),
        LocalDate.of(2017, 2, 20),
        LocalDate.of(2017, 5, 29),
        LocalDate.of(2017, 7, 4),
        LocalDate.of(2017, 9, 4),
        LocalDate.of(2017, 10, 9),
        LocalDate.of(2017, 11, 10),
        LocalDate.of(2017, 11, 23),
        LocalDate.of(2017, 12, 25)
    );
    HOLIDAYS = Collections.unmodifiableSet(new HashSet<>(dates));
  }

  public int getBusinessDays(LocalDate startInclusive, LocalDate endExclusive) {
    if (startInclusive.isAfter(endExclusive)) {
      String msg = "Start date " + startInclusive
          + " must be earlier than end date " + endExclusive;
      throw new IllegalArgumentException(msg);
    }
    int businessDays = 0;
    LocalDate d = startInclusive;
    while (d.isBefore(endExclusive)) {
      DayOfWeek dw = d.getDayOfWeek();
      if (!HOLIDAYS.contains(d)
          && dw != DayOfWeek.SATURDAY
          && dw != DayOfWeek.SUNDAY) {
        businessDays++;
      }
      d = d.plusDays(1);
    }
    return businessDays;
  }
}

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)

How to calculate the quantity of business days between two dates using Pandas

pd.date_range's parameters need to be datetimes, not series.

For this reason, we can use df.apply to apply the function to each row.

In addition, pandas has bdate_range which is just date_range with freq defaulting to business days, which is exactly what you need.

Using apply and a lambda function, we can create a new Series calculating business days between each start and current date for each row.

projects_df['start_date'] = pd.to_datetime(projects_df['start_date'])
projects_df['current_date'] = pd.to_datetime(projects_df['current_date'])

projects_df['days_count'] = projects_df.apply(lambda row: len(pd.bdate_range(row['start_date'], row['current_date'])), axis=1)

Using a random sample of 10 date pairs, my output is the following:

           start_date        current_date  bdays
0 2022-01-03 17:08:04 2022-05-20 00:53:46    100
1 2022-04-18 09:43:02 2022-06-10 16:56:16     40
2 2022-09-01 12:02:34 2022-09-25 14:59:29     17
3 2022-04-02 14:24:12 2022-04-24 21:05:55     15
4 2022-01-31 02:15:46 2022-07-02 16:16:02    110
5 2022-08-02 22:05:15 2022-08-17 17:25:10     12
6 2022-03-06 05:30:20 2022-07-04 08:43:00     86
7 2022-01-15 17:01:33 2022-08-09 21:48:41    147
8 2022-06-04 14:47:53 2022-12-12 18:05:58    136
9 2022-02-16 11:52:03 2022-10-18 01:30:58    175

---

Related Topics