# Exclude weekends and holidays from a formula.

1. ## 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?

2. ## Re: Exclude weekends and holidays from a formula.

Hi Stevey,

Post a sample workbook.

Cheers,

P.S. Welcome to the forum!

3. ## 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. ## 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

5. ## 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. ## 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

##### 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