Here's my columns:
Date Day (calculated formula) Data
11/1 Mon xyz
11/1 Mon xyz
11/2 Tue xyz
11/3 Wed xyz
11/4 Thur xyz
11/7 Mon xyz
11/14 Mon xyz
I would like to count how many Mondays were worked in a month (minus Holidays if possible).
Thanks.
Here's my columns:
Date Day (calculated formula) Data
11/1 Mon xyz
11/1 Mon xyz
11/2 Tue xyz
11/3 Wed xyz
11/4 Thur xyz
11/7 Mon xyz
11/14 Mon xyz
I would like to count how many Mondays were worked in a month (minus Holidays if possible).
Thanks.
Do you not understand what I need to do?
I need to count how many Mondays, Tuesdays, etc., are worked in a month based on the Date/Days/Data columns. Thanks.
=4+(DAY(D1-DAY(D1)+35)<WEEKDAY(D1-DAY(D1)-2))-SUMPRODUCT(--(TEXT(holidays,"y
yyymm")=TEXT(A1,"yyyymm")),--(WEEKDAY(holidays)=2))
where A1 holds a date in the month being tested, holidays is a range of
holiday dates.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"harnagel" <[email protected]> wrote in
message news:[email protected]...
>
> Here's my columns:
> Date Day (calculated formula) Data
>
> 11/1 Mon xyz
> 11/1 Mon xyz
> 11/2 Tue xyz
> 11/3 Wed xyz
> 11/4 Thur xyz
> 11/7 Mon xyz
> 11/14 Mon xyz
>
> I would like to count how many Mondays were worked in a month (minus
> Holidays if possible).
>
> Thanks.
>
>
> --
> harnagel
> ------------------------------------------------------------------------
> harnagel's Profile:
http://www.excelforum.com/member.php...o&userid=29376
> View this thread: http://www.excelforum.com/showthread...hreadid=490858
>
I guess let's just forget Holidays, I would like to count how many Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, & Sundays worked based on the data below:
Here's my columns:
Date Day (calculated formula) Data
11/1 Mon xyz
11/1 Mon xyz
11/2 Tue xyz
11/3 Wed xyz
11/4 Thur xyz
11/7 Mon xyz
11/14 Mon xyz
X Mondays
X Tuedays
X Wednesdays, etc.
Hi
With your dates in column A, set up the following in column E
E1 10/30, E2 10/31, E3 11/1, E4 11/2, E5 11/3, E6 11/4, E7 11/5
Format cells E1:E7 Format>Cells>Number>Custom> ddd
In cell F1 enter
=SUMPRODUCT(--(WEEKDAY($A$1:$A$100)=WEEKDAY(E1)))
Copy formula down through cells E2:E7
Change range A1:A100 to suit.
Regards
Roger Govier
harnagel wrote:
> I guess let's just forget Holidays, I would like to count how many
> Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, & Sundays
> worked based on the data below:
>
> Here's my columns:
> Date Day (calculated formula) Data
>
> 11/1 Mon xyz
> 11/1 Mon xyz
> 11/2 Tue xyz
> 11/3 Wed xyz
> 11/4 Thur xyz
> 11/7 Mon xyz
> 11/14 Mon xyz
>
> X Mondays
> X Tuedays
> X Wednesdays, etc.
>
>
Oh yes, and replace = 2 by =3 for Tue, =4 for Wed, etc.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"harnagel" <[email protected]> wrote in
message news:[email protected]...
>
> I guess let's just forget Holidays, I would like to count how many
> Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, & Sundays
> worked based on the data below:
>
> Here's my columns:
> Date Day (calculated formula) Data
>
> 11/1 Mon xyz
> 11/1 Mon xyz
> 11/2 Tue xyz
> 11/3 Wed xyz
> 11/4 Thur xyz
> 11/7 Mon xyz
> 11/14 Mon xyz
>
> X Mondays
> X Tuedays
> X Wednesdays, etc.
>
>
> --
> harnagel
> ------------------------------------------------------------------------
> harnagel's Profile:
http://www.excelforum.com/member.php...o&userid=29376
> View this thread: http://www.excelforum.com/showthread...hreadid=490858
>
Even if you didn't like my first attempt, you can still include holidays
=SUMPRODUCT((WEEKDAY(A2:A20)=2)*(NOT(ISNUMBER(MATCH(A2:A20,holidays,0)))))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"harnagel" <[email protected]> wrote in
message news:[email protected]...
>
> I guess let's just forget Holidays, I would like to count how many
> Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, & Sundays
> worked based on the data below:
>
> Here's my columns:
> Date Day (calculated formula) Data
>
> 11/1 Mon xyz
> 11/1 Mon xyz
> 11/2 Tue xyz
> 11/3 Wed xyz
> 11/4 Thur xyz
> 11/7 Mon xyz
> 11/14 Mon xyz
>
> X Mondays
> X Tuedays
> X Wednesdays, etc.
>
>
> --
> harnagel
> ------------------------------------------------------------------------
> harnagel's Profile:
http://www.excelforum.com/member.php...o&userid=29376
> View this thread: http://www.excelforum.com/showthread...hreadid=490858
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks