Quote Originally Posted by icestationzbra View Post
if i understand your requirement correctly, you would like to know the total number of workdays left in a month, considering that you work Monday through Saturday. if i have got that right, then try the following - inspired by @daddylonglegs:

put today's date (using CTRL+; or TODAY() function) or any other date in cell A1, then put this formula in B1 to get remaining workdays:

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6,7})+(EOMONTH(A1,0)-A1))/7))
for total workdays within that month - since the beginning of the month, try this in C1:

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6,7})+DAY(EOMONTH(A1,0)))/7))
these will work only if you have Analysis Toolpak installed.

the array {2,3,4,5,6,7} stands for Monday through Saturday.
Those both work perfectly, thanks!

omitting the day off gives me the ability to project days off for guys who are off sunday and tues, etc. and the 2nd formula is handy too. that tells them how many scheduled days they have to achieve their goals.

you da man.

I actually used JassonB's days remaining formula because it was independent of other cells, but your how many days this month to give me a total starting number for the different scenarios.

to get the days gone by, I modified the formula with a -1 in order to point it at LAST month:

Formula: copy to clipboard 
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&EOMONTH(TODAY(),-1))))={2,3,4,5,6}))-1


and then subtract a day to get the days sine the last day of LAST month.

This didn't work quite right.... I had to go to -2 to make the math work.... except it is wrong for days off monday people.... this has to be due to counting or not counting the current day, I suppose... but I would have thought Saturday would be the oddball, since as I write this, it is Saturday. I'm attaching the sheet if anyone wants to ferret out that wrinkle, or is you can use this as a solve.

how many working days.xlsx