Calculate # days in week based on date range

[Peelah]

Hi! Looking for some help with a formula for employee vacation requests.

I need to calculate the number of days each week that are requested off based on a vacation request that lists start and end dates. Vacation requests are listed in Columns A & B, and the weeks of the year are listed in Columns C - end.

For example:
A2: 2/2/16 (Employee first day off requested)
B2: 2/9/16 (Employee's last day off requested)

C1: 2/1/16 (want to automatically fill this out based on values in A2 & B2)
D1: 2/8/16
E1: 2/15/16
F1: 2/22/16
G1: 2/29/16
etc, etc

Desired Outcome:
C1: 4
D1: 2


I've come up with this formula, but it doesn't work if a vacation request starts or ends mid week.
=IF(AND($A2<=C$1,$B2>(C$1+6)),NETWORKDAYS(C$1,D$1)-1,0)

Any help will be much appreciated!

 

[DILIPandey]

Hi Peelah,

A2: 2/2/16 (Employee first day off requested)
B2: 2/9/16 (Employee's last day off requested)

C1: 2/1/16 (want to automatically fill this out based on values in A2 & B2)

How you got 2/1/16 ? Are you looking forward to show first monday as week here ?


Regards,
DILIPandey

 

[Peelah]

Hi Peelah,



How you got 2/1/16 ? Are you looking forward to show first monday as week here ?


Regards,
DILIPandey

Yes, Columns C to the end will list the Monday of each week.

 

[DILIPandey]

Enter below formula in C2:-

=A2-WEEKDAY(A2,2)+1

and fill down.

It will give you the first monday of the vacation period. Thanks.

Regards,
DILIPandey