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!

 

[Peter_SSs]

go2jared

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

Excel Workbook
	A	B	C
1	19/08/2007	Today
2	11	Days left in month excluding Sundays
3
Work Days

 

[go2jared]

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]

Here is another version: without Analysis Tool Pak an excluding holidays

feriados.xls
	A	B	C	D	E
1	19/08/2007	31/08/2007		Holidays
2	10			22/08/2007
3				26/08/2007
4
5
Hoja1

galileogali

 

[barry houdini]

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]

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]

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]

galileogali:

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

 

[galileogali]

HOTPEPPER: Thanks for your OBVIOUS comment.

GALILEOGALI

 

[barry houdini]

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