Counting Workdays & Sundays with Holidays

  • Thread starter Thread starter Polos
  • Start date Start date
P

Polos

I want to know how many days are between two dates with the nex
conditions:
How many Monday-Friday(s) without Holidays.
How many Saturday(s) without Holidays.
How many Sunday(s) including Holiday(s) in the period.

The very first condition I solved with NETWORKDAY function, but I hav
no idea of the last two conditions.

Any help would be appreciated.

Thanks
 
I want to know how many days are between two dates with the next
conditions:
How many Monday-Friday(s) without Holidays.
How many Saturday(s) without Holidays.
How many Sunday(s) including Holiday(s) in the period.

The very first condition I solved with NETWORKDAY function, but I have
no idea of the last two conditions.

Any help would be appreciated.

Thanks.
Click to expand...

Saturdays: =SUM(1*(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))=7))

This is an array formula. Hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

Sundays: =SUM(1*(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))=1))

Also an array formula.

Holidays (and I assume you mean holidays that do not fall on a Sat or Sunday):

EndDate-StartDate+1-SUM(Workdays,Sundays,Saturdays)


--ron
 
Ron,

My problem is to calculate how many Saturday(s) are in a period bu
excluding a holiday that fall on a Saturday.

If that period has a holliday that fall in a Saturday it must b
counted as a extra Sunday (which includes Holidays).

If Holliday fall on a Sunday it must be counted only as a Sunday.

For example:

December 2004:
Unique Holiday: Dec 25,2004 (Sat)
Mon-Fri: 23 days
Saturday: 3 days
Sundays+Holidays: 5 days

January 2007
Holiday: Jan 1, 2007 (Mon)
Mon-Fri: 22 days
Saturday: 4 days
Sunday+Holiday: 5 days

January 2006
Holiday: Jan 1, 2006 (Sun)
Mon-Fri: 22 days
Saturday: 4 days
Sunday+Holiday: 5 days

Thank
 
Polos,

Your examples show periods as 1 month length : is that always the case?

What do you have as entry (start and ending dates OR year and month number)?

Regards,

Daniel M.
 
Daniel,

I could use any starting & ending dates, so it is not necessarily just
one month period.

Regards,
 
Hi,

With :
A1: StartDate
A2: EndDate
Holidays: A range containing your holidays

Saturdays without Holidays:
=SUM(ISNA(MATCH(ROW(INDIRECT(A1&":"&A2)),Holidays,0))*
(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

Sundays + anyHolidays:
=SUM(ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),Holidays,0))*
(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>1))+INT((A2-WEEKDAY(A2)-A1+8)/7)

Both are ARRAY formulas so you need to enter them with Ctrl-Shift-Enter.

Regards,

Daniel M.
 
Ron,

My problem is to calculate how many Saturday(s) are in a period but
excluding a holiday that fall on a Saturday.

If that period has a holliday that fall in a Saturday it must be
counted as a extra Sunday (which includes Holidays).

If Holliday fall on a Sunday it must be counted only as a Sunday.

For example:

December 2004:
Unique Holiday: Dec 25,2004 (Sat)
Mon-Fri: 23 days
Saturday: 3 days
Sundays+Holidays: 5 days

January 2007
Holiday: Jan 1, 2007 (Mon)
Mon-Fri: 22 days
Saturday: 4 days
Sunday+Holiday: 5 days

January 2006
Holiday: Jan 1, 2006 (Sun)
Mon-Fri: 22 days
Saturday: 4 days
Sunday+Holiday: 5 days

Thanks
Click to expand...

OK, that's a better description:

Workdays

=NETWORKDAYS(StartDate,EndDate,Holidays)

The following are array formulas, entered with <ctrl><shift><enter>

Saturdays not Holidays

=SUM(1*(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))=7))-
SUM((WEEKDAY(Holidays)=7)*(Holidays>=StartDate)*(Holidays<=EndDate))

Sundays+Holidays

=SUM(1*(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)))=1))+
SUM((WEEKDAY(Holidays)<>1)*(Holidays>=StartDate)*(Holidays<=EndDate))


--ron
 
Back
Top