+ Reply to Thread
Results 1 to 3 of 3

Generate list of dates between dates based on criteria - Onsite/Offshore holidays

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Generate list of dates between dates based on criteria - Onsite/Offshore holidays

    Hi There,

    I am working on a sheet where we are trying to generate revenue plan and forecast.

    Requirement:
    1. Generate list of dates between two dates:
    1a. Exclude holidays for onsite and offshore based on the resource (Onsite/Offshore)
    1b. Generate list based on one more value Plan and Forecast.

    2. Along with the dates, few more columns should also be added to the generated rows from the main sheet

    I am bad at using arrays and can manage only at basic macro level. The above solution will save about 3-4 four hours a day we are spending to generate spreadsheets further.

    Attached is the sample spreadsheet for reference.

    Regards,
    Srikanth
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,056

    Re: Generate list of dates between dates based on criteria - Onsite/Offshore holidays

    Here's a partial answer based on my understanding of the requirement. It computes the NETWORKDAYS based on whether Offshore or OnSite is selected. I do not understand the rest of the requirement.

    First, split the holidays into two groups as indicated in this spreadsheet. For sake of clarity, give each group of holidays a name. I chose On_Holiday and Off_Holiday. I also made these names dynamic so you can add or delete holidays in each group.

    Then the NETWORKDAYS formula becomes: =NETWORKDAYS(D2,E2,IF(G2="Offshore",Off_Holiday,On_Holiday))
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-11-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    58

    Re: Generate list of dates between dates based on criteria - Onsite/Offshore holidays

    He dflak,

    Thank you for the response, for some reason I did not get notification and we somehow sorted it out.
    I should check this one and understand how it works.

    Thank you so much for the reply and apologise for not responding earlier.

    Regards,
    Srikanth
    Last edited by sreeks; 08-21-2017 at 12:41 AM.

+ 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] take list of dates, and merge into larger table based on criteria
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-15-2016, 03:25 PM
  2. How to take a list of dates and generate them into a calendar
    By lcooper011 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2016, 04:25 PM
  3. [SOLVED] Subtracting 2 dates, exclude Sundays & Holidays (in list) - outside US
    By husni in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-19-2014, 10:11 PM
  4. Replies: 6
    Last Post: 09-09-2013, 08:54 AM
  5. VBA - List Dates excluding Weekends and Holidays
    By tykhoo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2012, 11:13 PM
  6. List Dates Excluding Holidays but Including weekends
    By GuyHudson in forum Excel General
    Replies: 6
    Last Post: 09-22-2010, 05:58 AM
  7. Finding weekends and holidays in a list of dates
    By mat13wat in forum Excel General
    Replies: 2
    Last Post: 08-04-2007, 07:02 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