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?

Hi Stevey,

Post a sample workbook.

Cheers,

P.S. Welcome to the forum!

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

C35: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--(C$4:C$33<>""))
C36: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--(C$4:C$33="Not in Use"))
both copied across to column J

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

Yes.

If we look at your existing NETWORKDAYS function:

C38: =NETWORKDAYS(B4,B33,L15:L18)

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

C35: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--ISNA(MATCH($B$4:$B$33,$L$15:$L$18,0)),--(C$4:C$33<>""))
C36: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--ISNA(MATCH($B$4:$B$33,$L$15:$L$18,0)),--(C$4:C$33="Not in Use"))

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.