How to calculate non-continuous workdays in Excel

[feltonam]

I am trying to report on the number of actual workdays between dates, excluding weekends and Bank Holidays. I can use a modified Networkdays formula in Excel, but only if the Work pattern is for continuous days of the week ie: Sunday to Thursday. Problem is that we have staff on work patterns with non-continuous days ie: Monday, Wednesday, Friday, Saturday. Any ideas welcome.

 

[Gavona]

NETWORKDAYS(start_date,end_date,holidays)
Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

Create named ranges for each work pattern defining the days that are to be treated as holidays. Should be easy to populate them. You can set the range to automatically expand as you add more dates (see FAQs).
Then:
=NETWORKDAYS($A5,$B5,Pattern1)

Regards,

Gavin

 

[SkipVought]

Hi,

Yet another approch.

1. in a new column labled DOW (day of week), wnter formula
[tt]
=TEXT(YourDateRef,"DDD")
[/tt]
2. on separate sheet, for instance, in A1 & A2 enter the START and END dates you are interested in calculating work days.

3. in B1 thru E1, enter
[tt]
Mon Wed Fri Sat
[/tt]
[tt]
B2: =SUMPRODUCT((Dte>=$A1)*(Dte<=$A2)*(dow=B1))
[/tt]
copy across and then SUM to get the TOTAL days.

VOLA, y'all!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feltonam]

Hi both, and thanks for responses. Tried yours Gavin - if someone works Wednesday to Sunday for example, I set up a range containing only Mondays and Tuesdays and use this in place of HOLIDAYS. However as NETWORKDAYS excludes Saturday and Sunday by default these days are also then excluded when I want them to be counted. Perhaps I am missing something really obvious, or I have misunderstood?

I can modify Networkdays to recognise different weekend days, ie: for Friday and Saturday: NETWORKDAYS(A1+1,A2+1) but I have not yet got this to work properly along with the range.