+ Reply to Thread
Results 1 to 6 of 6

Exclude weekends and holidays from a formula.

  1. #1
    Registered User
    Join Date
    08-26-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Smile Exclude weekends and holidays from a formula.

    Can anyone help? I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?
    Attached Files Attached Files
    Last edited by Stevey; 08-28-2009 at 07:04 PM.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Exclude weekends and holidays from a formula.

    Hi Stevey,

    Post a sample workbook.

    Cheers,

    P.S. Welcome to the forum!

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Exclude weekends and holidays from a formula.

    Not entirely sure I follow... but based on my understanding you need to revise formulae in rows 35 & 36, ie:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-26-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Re: Exclude weekends and holidays from a formula.

    Hi,

    Forgive me for the lack of clarity but you nailed it and it works great but could that code be modified somehow, to exclude Bank Holidays as well? ie Xmas day, new years day etc

    Regards, Stevey
    Last edited by Stevey; 08-28-2009 at 06:25 PM. Reason: Signature added

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Exclude weekends and holidays from a formula.

    Yes.

    If we look at your existing NETWORKDAYS function:

    Please Login or Register  to view this content.
    we can see you've assigned range L15:L18 to be the public holiday range (ie the range in which public holiday dates are to be listed) -- for the sake of demo let's continue with that being the range

    Please Login or Register  to view this content.
    In reality your public holiday range will be bigger (to encompass all public holidays) but you can revise ranges as and when - the above is just a demo of concept.

  6. #6
    Registered User
    Join Date
    08-26-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Thumbs up Re: Exclude weekends and holidays from a formula.

    Hi,

    It works a treat. I appreciate the help you have given me. It is the first time I have used a forum and it has been a great experience. I thank you once again.

    Regards Stevey

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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