calculate working days to end of the month

[absquatulation]

Hi Guys,


OK, so I can calculate the number of days to the end of the month

=(DATE(YEAR(ref),MONTH(ref)+1,0)-(ref1+(DATE(YEAR(ref),MONTH(ref),0))))

but, how do I calculate the number of "working days" to the end of the month??

any ideas, 'cos it's floored me.

Thanks,

Simon

 

[MichaelRo]

Could you use NETWORKDAYS?

Its part of the Analysis ToolPack Add-In

 

[absquatulation]

As usual working for a large organisation, the IT (Idiot Team) department actively discourage using any add-ins or bolt on to any software.

We don't have it and hell will freeze over before they allow it onto "their" system.

 

[MichaelRo]

As far as I was aware the Analysis ToolPack is part of excel anyway...?

For it not to be included I would suggest your IT department did a custom or minimal install of Excel..

 

[barry houdini]

To give you the number of working days from a date in A1 to the end of the month (including A1 if a weekday) I was going to suggest

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

as per Michael's suggestion but if you can't use Analysis ToolPak you could try

=SUMPRODUCT(--(WEEKDAY(A1+ROW(1:31)-1,2)<6),--(MONTH(A1+ROW(1:31)-1)=MONTH(A1)))

..of course NETWORDAYS has the advantage in that you can also exclude holidays if you wish...

 

[absquatulation]

MikaelRo

I agree, but IT work on the theory of "they know best", rather than the user might just know what they want.

I have no access to add-ins, or even VBA Help.



barry houdini

Thanks Barry, I'll try it.