+ Reply to Thread
Results 1 to 5 of 5

Calculate # days in week based on date range

  1. #1
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    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. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,596

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,596

    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. #4
    Registered User
    Join Date
    03-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Calculate # days in week based on date range

    Thank you so much! This works like a charm!

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,596

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to analyze specific days of the week within date range?
    By hopegriffin in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-04-2016, 10:33 PM
  2. [SOLVED] Find week days within the date range and return number
    By CaineSmith in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-06-2014, 03:47 PM
  3. Replies: 2
    Last Post: 10-06-2013, 02:03 PM
  4. [SOLVED] Sum 7 days of information based on week ending date
    By tatyanamarie in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-18-2013, 07:22 AM
  5. [SOLVED] Calculating a week of dates/days based on the beginning date entered...
    By tiffany04530 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-27-2012, 09:55 AM
  6. Separating days by Year/Week in a date range :)
    By scotinexcile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 09:26 PM
  7. Use VBA to calculate week cycle based on date
    By batteredveg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2012, 11:06 AM

Tags for this Thread

Bookmarks

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