Excel Date Count (within a range)

ssmacc · 12 Apr 2016 at 13:13

Hello Excel experts!

Is it possible to count the number of working days between a given range that are in a given month?

For example:
A1 = 11/04/2016
B1 = 11/05/2016

There are 22 working days between A1 and B1 (formula below)

=NETWORKDAYS(A1,B1,K$2:K$97)

K$2:K$97 contains bank holidays which are removed from the count.

However, if it’s possible and I can understand it! I’d like to split this out to show worked days in April and worked days in May? (using the dates above the following number of days have been worked in April and May)

C1 = April
D1 = 15

C2 = May
D2 = 7

Hope this makes sense!
Cheers

Grrrrr · 13 Apr 2016 at 10:22

In your formula you just need to wrap MAX() and MIN() functions around the A1 and B1 parts, along with the first and last dates of the month.

e.g. For April

=NETWORKDAYS(MAX(A1,DATEVALUE("01/04/2016")),MIN(B1,DATEVALUE("30/04/2016")),K$2:K$97)

I've hard coded the first and last days of April in there, but you can replace that with a formula

ssmacc · 13 Apr 2016 at 21:54

Thanks Grrrrr, will give this a go tomorrow! (might have popped out for a pint earlier)

ssmacc · 14 Apr 2016 at 17:48

Works brilliantly and really helped.

Much appreciated Grrrrr!

Cheers