Converting months to weekdays and workdays for any given year

[Cary]

I'm hung up on a simple calculation: Col A lists 12-months Col B lists
Workdays per Month, Col c is Year

How do I calculate the number of workdays per month? The number of
weekdays per month?

Thanks

 

[Ron Rosenfeld]

I'm hung up on a simple calculation: Col A lists 12-months Col B lists
Workdays per Month, Col c is Year

How do I calculate the number of workdays per month? The number of
weekdays per month?

Thanks

Check HELP for the NETWORKDAYS function.

If your Col A months are sequential, in month order, and in A2:A13, then you
could:

B2: =NETWORKDAYS(DATE(C2,ROWS($1:1),1),DATE(C2,ROWS($1:1)+1,0))

Format the result as General or as Number with 0 decimals.



--ron

 

[Ron Rosenfeld]

Check HELP for the NETWORKDAYS function.

If your Col A months are sequential, in month order, and in A2:A13, then you
could:

B2: =NETWORKDAYS(DATE(C2,ROWS($1:1),1),DATE(C2,ROWS($1:1)+1,0))

Format the result as General or as Number with 0 decimals.



--ron

The above formula gives the number of WEEKDAYS per month.

To obtain the number of WORKDAYS, you'd need to use the optional HOLIDAY
argument for NETWORKDAYS, and have a list of holidays someplace.


--ron

 

[Cary]

Thanks for you prompt reply.

It seems to have worked but I'm baffled by the ROWS function

 

[Ron Rosenfeld]

Thanks for you prompt reply.

It seems to have worked but I'm baffled by the ROWS function

ROWS($1:1) resolves to a "1" which, within the DATE function, represents the
month.

As you copy/drag it down, because of the mixed absolute/relative reference, it
adjust to:

ROWS($1:2)
ROWS($1:3)

which resolves to

2
3
etc.

2= February
3= March
and so forth.

Just a simple way of automatically adding one to that argument of the DATE
function.

There are other ways of constructing the necessary date, but this one seemed
efficient given your layout.
--ron