# calculate working days to end of the month

#### absquatulation

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Could you use NETWORKDAYS?

Its part of the Analysis ToolPack Add-In

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.

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..

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...

MikaelRo

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

barry houdini

Thanks Barry, I'll try it.

Replies
11
Views
242
Replies
5
Views
71
Replies
2
Views
174
Replies
16
Views
544
Replies
7
Views
108

1,203,108
Messages
6,053,561
Members
444,673
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back