Dislpay count of workdays in a month?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DB Explorer - 21 Mar 2006 16:34 GMT

I have an excel sheet with the month and year in the A row..Like

Jan-06 Feb-06 Mar-06 Apr-06

How do i insert a formula in B row, to display the number of weekdays
in that corresponding month, like this?

Jan-06 Feb-06 Mar-06 Apr-06
22 20 23 20

Please advise

Cheers
Sunny

Reply to this Message

daddylonglegs - 21 Mar 2006 17:02 GMT

Here's one way....

assume that your your row 1 contains dates - the first day of eac
month - formatted as mmm-yy, with Jan-06 in A1

in A2 use this formula

=NETWORKDAYS(A1,EOMONTH(A1,0))

formatted as general, copied across row.

Note both NETWORKDAYS and EOMONTH are part of Analysis ToolPak add-in
An alternative formula which doesn't require Analysis ToolPak

=32-DAY(A1+31)-SUM(INT((WEEKDAY(A1-{0,1})+31-DAY(A1+31))/7)

Reply to this Message

Duke Carey - 21 Mar 2006 17:19 GMT

Make sure you have the Analysis Toolpak isntalled (Tools->Add-ins.. and make
sure the Analysis Toolpak is checked), then use the formula

=networkdays(--"1/1/2006",--"2/1/2006")

If A1 & B1 contain the dates Jan 1 & Feb 1 of 2006, use

=networkdays(A1,B1)

> I have an excel sheet with the month and year in the A row..Like
>
[quoted text clipped - 10 lines]
> Cheers
> Sunny

Reply to this Message