formula for total days, excluding holidays but including weekends

I need a formula to calculate total days (working days) that excludes
holidays, but includes weekends. The networkdays formula doesn't work since
we work on weekends and have variable days off. I wanted to put the select
days off( some weekdays and some weekends in a holiday range), and have them
subtracted from the start date, end date range if they fall in the range.

T
Wed May 07 22:21:14 CDT 2008

Try this...

List off dates and holiday dates in a range of cells, say, H1:H10

A1 = start date
B1 = end date

=B1-A1+1-MEDIAN(FREQUENCY(H1:H10,A1:B1-{1,0}))

Format as General or Number

--
Biff
Microsoft Excel MVP


"wtfisch" <wtfisch@discussions.microsoft.com> wrote in message
news:2320F911-F4DC-435C-9904-6BD967E3EAF1@microsoft.com...
>I need a formula to calculate total days (working days) that excludes
> holidays, but includes weekends. The networkdays formula doesn't work
> since
> we work on weekends and have variable days off. I wanted to put the
> select
> days off( some weekdays and some weekends in a holiday range), and have
> them
> subtracted from the start date, end date range if they fall in the range.
>

T
Wed May 07 22:37:27 CDT 2008

Correction:

> =B1-A1+1-MEDIAN(FREQUENCY(H1:H10,A1:B1-{1,0}))

Use this one:

=B1-A1+1-INDEX(FREQUENCY(H1:H6,A1:B1-{1,0}),2)

--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@comcast.net> wrote in message
news:OJoqUqLsIHA.2492@TK2MSFTNGP06.phx.gbl...
> Try this...
>
> List off dates and holiday dates in a range of cells, say, H1:H10
>
> A1 = start date
> B1 = end date
>
> =B1-A1+1-MEDIAN(FREQUENCY(H1:H10,A1:B1-{1,0}))
>
> Format as General or Number
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "wtfisch" <wtfisch@discussions.microsoft.com> wrote in message
> news:2320F911-F4DC-435C-9904-6BD967E3EAF1@microsoft.com...
>>I need a formula to calculate total days (working days) that excludes
>> holidays, but includes weekends. The networkdays formula doesn't work
>> since
>> we work on weekends and have variable days off. I wanted to put the
>> select
>> days off( some weekdays and some weekends in a holiday range), and have
>> them
>> subtracted from the start date, end date range if they fall in the range.
>>
>
>

wtfisch
Thu May 08 12:26:01 CDT 2008



"T. Valko" wrote:

> Correction:
>
> > =B1-A1+1-MEDIAN(FREQUENCY(H1:H10,A1:B1-{1,0}))
>
> Use this one:
>
> =B1-A1+1-INDEX(FREQUENCY(H1:H6,A1:B1-{1,0}),2)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message
> news:OJoqUqLsIHA.2492@TK2MSFTNGP06.phx.gbl...
> > Try this...
> >
> > List off dates and holiday dates in a range of cells, say, H1:H10
> >
> > A1 = start date
> > B1 = end date
> >
> > =B1-A1+1-MEDIAN(FREQUENCY(H1:H10,A1:B1-{1,0}))
> >
> > Format as General or Number
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "wtfisch" <wtfisch@discussions.microsoft.com> wrote in message
> > news:2320F911-F4DC-435C-9904-6BD967E3EAF1@microsoft.com...
> >>I need a formula to calculate total days (working days) that excludes
> >> holidays, but includes weekends. The networkdays formula doesn't work
> >> since
> >> we work on weekends and have variable days off. I wanted to put the
> >> select
> >> days off( some weekdays and some weekends in a holiday range), and have
> >> them
> >> subtracted from the start date, end date range if they fall in the range.
> >>
> >
> >
>
>
>