# Calculate # days in week based on date range

1. ## Calculate # days in week based on date range

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!

2. ## Re: Calculate # days in week based on date range

Welcome to Excel Forum.
If I understand this correctly then I believe you would actually like the number of vacation days to be displayed in row 2. If that is correct paste this formula which shows the logic behind the calculation, in C2 and copy across:
``Please Login or Register  to view this content.``
Alternately you may want to use the following formula in C2 and across:
``Please Login or Register  to view this content.``
Here is a file that has both formulas applied: IF or MIN vacation day counter.xlsx
Let me know if you have any questions.

3. ## Re: Calculate # days in week based on date range

I overlooked the part where you wanted to work with the NETWORKDAYS function. Substitute these formulas in C2 and C4, respectively, and across in the file attached to post #2:
``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``
Let me know if you have any questions.

4. ## Re: Calculate # days in week based on date range

Thank you so much! This works like a charm!

5. ## Re: Calculate # days in week based on date range

You're Welcome and thank you for the feedback. If you haven't already please take a moment and mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1