Calculate working days left in current month

[go2jared]

Hello,

Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule.

We are open M-F and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically.

On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month.

Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08-20-2007). Is there a way to get Excel to do that? Thanks!

 

[barry houdini]

Re: Another method...

=NETWORKDAYS(Start,End,Holidays)+SUM(IF(WEEKDAY(Start-1+ROW(INDIRECT("1:"&TRUNC(End-Start)+1)))=7,1,0))

Hello Tim,

To count Saturdays between start and end you can use a simpler formula, similar to part of my suggested formula above, i.e.

=INT((WEEKDAY(start)+end-start)/7)

but just adding that to the NETWORKDAYS formula won't always give the correct result because it won't exclude any holidays which fall on a Saturday

 

[galileogali]

Barri,
Excellent. Now you have understood me exactly



GALILEOGALI

 

[go2jared]

Thank you all for the GREAT feedback. I created my spreadsheet for my business and most people do not have the "Analysis Toolpack" installed on their Excel. It works great, but I think I will go with go with galileogali's suggested method. I am still learning and these formulas seem somewhat complex. The copy and paste function, with tweaks to the cell references has proven invaluable.

This board is GREAT and I have received a lot of valuable information from it. Thank you all for your help. I love to see things work the way that I intend for them to.

 

[Tim Francis-Wright]

Re: Another method...

=NETWORKDAYS(Start,End,Holidays)+SUM(IF(WEEKDAY(Start-1+ROW(INDIRECT("1:"&TRUNC(End-Start)+1)))=7,1,0))

Hello Tim,

To count Saturdays between start and end you can use a simpler formula, similar to part of my suggested formula above, i.e.

=INT((WEEKDAY(start)+end-start)/7)

but just adding that to the NETWORKDAYS formula won't always give the correct result because it won't exclude any holidays which fall on a Saturday

Yes, that is right. But I presumed that (US) federal holidays will not fall on Saturdays--still, it makes sense not to have to presume that!