# Calculate working days left in current month

#### go2jared

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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Peter_SSs

##### MrExcel MVP, Moderator
go2jared

Try this. Note that the EOMONTH formula used requires the AnalysisToolPak to be installed and activated. Check in Tools|Add-Ins...

Excel Workbook
ABC
119/08/2007Today
211Days left in month excluding Sundays
3
Work Days

#### go2jared

##### New Member
Wow! Thank you! I would not have figured that out independently. I did enable the toolpack you suggested and change the A1 to E3 (where the current date is), but it worked, beautifully. My only question at this point is if there is a day that we are closed due to a federal holiday. I am sure that there are always variables that no formula can account for. Excel is a great program, but it does have its limits as do I. Thank you again!

#### galileogali

##### Well-known Member
Here is another version: without Analysis Tool Pak an excluding holidays
ABCDE
119/08/200731/08/2007Holidays
21022/08/2007
326/08/2007
4
5
Hoja1

galileogali

#### barry houdini

##### MrExcel MVP
If you have today's date in E3 then use a formula as suggested by galileogali to get the last day of the month, e.g. in F3

=DATE(YEAR(E3),MONTH(E3)+1,0)

then, assuming you have a named range called holidays containing a list of all federal holidays then this formula will give the number of working days (including Saturdays) left in the month (including today)

=F3-E3-INT((1-WEEKDAY(F3)+F3-E3)/7)-SUMPRODUCT(--(WEEKDAY(holidays)<>1),--(holidays>=E3),--(holidays<=F3))

#### galileogali

##### Well-known Member
Very well barri.
It seems important to me to emphasize the certain function of “1” in your formulates.
Weekday (“sunday”) = 1
Then if the day of the week to exclude Friday outside, like WeekDay (“Friday”) = 6,
it corresponds

=F3-E3-INT((.....WEEKDAY("FRIDAY")......-WEEKDAY(F3)+F3-E3)/7)-SUMPRODUCT(--(WEEKDAY(holidays)<>1),--(holidays>=E3),--(holidays<=F3))
or MONDAY,,,,TUESDAY

GALILEOGALI

#### Tim Francis-Wright

##### Board Regular
Another method...

Another method would be to have the Analysis ToolPak function NETWORKDAYS do most of the work and then add the number of Saturdays on the end.

Chip Pearson's formula to do this is short enough but is an array formula, so it requires a Control-Shift-Enter method for entry. And the holidays list needs to be large enough to cover the ranges that you will use for your dates.

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

Start is the starting date
End is the ending date
Holidays is the range with the holidays in it.

#### Scott Huish

##### MrExcel MVP
galileogali:

WEEKDAY("Friday") is not valid, WEEKDAY expects a date.

#### galileogali

##### Well-known Member
HOTPEPPER: Thanks for your OBVIOUS comment.

GALILEOGALI

#### barry houdini

##### MrExcel MVP
Hello galileogali

If I understand you correctly you are saying that you could exclude different days of the week by changing the bolded 1s here for other numbers, e.g. 6 to exclude Fridays.

=F3-E3-INT((1-WEEKDAY(F3)+F3-E3)/7)-SUMPRODUCT(--(WEEKDAY(holidays)<>1),--(holidays>=E3),--(holidays<=F3))

My suggested formula doesn't quite work like that....

If I changed it to this

=F3-E3+1-INT((WEEKDAY(E3-1)+F3-E3)/7)-SUMPRODUCT(--(WEEKDAY(holidays)<>1),--(holidays>=E3),--(holidays<=F3))

then you can change the two bolded 1s for another number, e.g. 2 to count all days except Mondays

Replies
6
Views
66
Replies
3
Views
79
Replies
3
Views
347
Replies
1
Views
103
Replies
4
Views
95

1,191,280
Messages
5,985,731
Members
439,978
Latest member
Mr930R

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