## Issue

Excel has NETWORKDAYS() function that find the number of business days between two dates.

Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn’t have to deal with holidays.

## Solution

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)
```

Answered By – Rodger Bagnall

**This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 **