Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
=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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
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. -- harnagel ------------------------------------------------------------------------ harnagel's Profile: http://www.excelforum.com/member.php...o&userid=29376 View this thread: http://www.excelforum.com/showthread...hreadid=490858 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
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 FormatCellsNumberCustom 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
Even if you didn't like my first attempt, you can still include holidays
=SUMPRODUCT((WEEKDAY(A2:A20)=2)*(NOT(ISNUMBER(MATC H(A2:A20,holidays,0))))) -- HTH RP (remove nothere from the email address if mailing direct) "harnagel" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Mondays worked in Month
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help!!! Vlookup!! | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Count number of days in given month? | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
How do I count these? | Excel Worksheet Functions |