formula for total days, excluding holidays but including weekends

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wtfisch - 08 May 2008 03:55 GMT

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.

Reply to this Message

T. Valko - 08 May 2008 04:21 GMT

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

Signature

Biff
Microsoft Excel MVP

>I need a formula to calculate total days (working days) that excludes
> holidays, but includes weekends. The networkdays formula doesn't work
[quoted text clipped - 4 lines]
> them
> subtracted from the start date, end date range if they fall in the range.

Reply to this Message

T. Valko - 08 May 2008 04:37 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Try this...
>
[quoted text clipped - 15 lines]
>> them
>> subtracted from the start date, end date range if they fall in the range.

Reply to this Message

wtfisch - 08 May 2008 18:26 GMT

> Correction:
>
[quoted text clipped - 23 lines]
> >> them
> >> subtracted from the start date, end date range if they fall in the range.

Reply to this Message