total days in month minus all sundays

[wilsonsims]

i need to figure how many working days are in any given month. my working days are only monday thru saturday. sometimes i also need to exlude holidays. can i use the networkingdays excluding all sundays?

many thanks for your time & assistance!

wilsonsims
www.grayauto.com

[Peo Sjoblom]

One way

=(DATE(YEAR(A1),MONTH(A1)+1,)-A1+1)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":
"&DATE(YEAR(A1),MONTH(A1)+1,))),2)=7))

where you put the first date of the month in A1

--

Regards,

Peo Sjoblom

"wilsonsims" <wilso...@yahoo.com> wrote in message
news:3C4A14D4-197E-46EF...@microsoft.com...

[Frank Kabel]

Hi
if the start of your month is in cell A1 and the end of your month is
in B1 try the following array formula (entered with CTRL+SHIFT+ENTER)

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:
"&TRUNC(B1-A1)+1)))=1,1,0))

--
Regards
Frank Kabel
Frankfurt, Germany

[Ron Rosenfeld]

For your workdays, the *array* formula:

=DAY(A1+32-DAY(A1+32))-SUM(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:"&DAY(A1+32-DAY(A1+32))))))=1))

To enter an *array* formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.

A1 contains a date (any date) during the month of interest.

--ron

[Norman Harker]

Hi Wilson!

Here's an array entered approach using the month of the date in A1:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-SUM(IF(WEEKDAY(DATE(YEAR(A1),MONTH(
A1),1)+ROW(INDIRECT("1:"&TRUNC(DATE(YEAR(A1),MONTH(A1)+1,0)-DATE(YEAR(
A1),MONTH(A1),1)))))=1,0))
Entered by pressing and holding down Ctrl + Shift and then pressing
Enter.

I'll break it up as you can then modify for similar problems like
counting the number of pay days until Christmas.

It's based on two formulas found at:

Chip Pearson:
http://www.cpearson.com/excel/DateTimeWS.htm

Days in a month:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Number of a given day in a period:
=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
Array entered.
Where A2 is the start_date; B2 the end date and C2 the day number
(Sunday =1, Monday = 2 etc)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"wilsonsims" <wilso...@yahoo.com> wrote in message
news:3C4A14D4-197E-46EF...@microsoft.com...