How many days left in the current month

I did a search to see if I could find this already solved, but after reading 6 or 7 threads, none quite fit, and I'm in no rush, so...

What I need to do is pull just the day portion of a date in order to subtract it from the number of working days in a month.

I use 27 days as my 'month' ...that is how many days we are typically open. I like to base our goals on daily production, so I want to break our monthly objectives into daily... and I'd like to have this sheet alter the required pace as production results are compared to goals.

anyone know how to yoink the 13 out of 11/13/2012? If I could figure that out, I'd be able to cobble the rest together.


thankee

Lj

doh. nevermind. cell A1= today() cell A2 = day(A1). duh.

yeah... the day() thing. i want it to do it based on the day I'm looking at the sheet, so the today() in the first cell, makes that fly. thanks!

is there a function that will give you how MANY days are in the current month? It wouldn't be a big deal to do that manually one time, and refer to it, but you guys know cool tricks.

even better! slick! thanks!

One way:

=DAY(EOMONTH(TODAY(),0))

hmm... yes, that works.... now I find that I can't make what I want to happen...happen.

Ideally, what I'd like to happen is this:

we are open monday thru saturday. I'd like to be able to automatically calculate how many work days there are in a month, first of all... and then how many remain from 'today'.... I could just use 27, as it is usually the right answer... but my inner nerd wants that to be precise.

IF i can get that expressed as a simple 2digit number, I'd like for it to figure out, from the position of TODAY, how many 'open' days remain in the current month.

for example, if I open the sheet on the 13 of November, I'd like it to be able to tell me how many days, counting only monday-saturdays remain, counting the present day.

once I get that set, I'll want to be able to adjust that to the salesmen's schedules .... and they are off one of the open days each, and days off vary.

anyone done this kind of thing before?

that formula yields the number 21. as today is the 9th, and there are 30 days in Nov, it is evidently giving me the total number of days, without respect to a 6 day work week.

is there a function that identifies which week of the month we are currently in? I know there's one that does week of the year.....

also, I think I'll need to be able to identify the days of the week. I saw somewhere that sunday is '11' ...I can't remember where. maybe a countif calculation that would number the sundays, mondays, tuesdays that have passed as of today()....

If your date is in A1 (or just replace with TODAY())
=25+MAX(0,DAY(EOMONTH(A1,0))-29)-INT((DAY(EOMONTH(A1,0))-WEEKDAY(EOMONTH(A1,0)))/28)

thanks, Paul....but that formula returns the # 26 with or without today() in field a1.

could you step me through it so I understand what it's doing, and figure out where to adjust it?

That formula tells you how many days in that month are not Sunday (your workday). So, November (Today) returns 26. That is the first part of your request ("I'd like to be able to automatically calculate how many work days there are in a month, first of all"). It should be able to be modified to your second goal by changing that initial 25.

Ok, which 0 is the Sunday? I'd like to be able to modify that to give me the same answer for guys that have Sunday and Tuesday off, and Sunday and Weds off, etc.

Your formula bamboozles me. IF you have the time, could you explain the features of it? Where did the numbers 25, 29 and 28 come from? do you just know that??or is it particular to this November of 2012...or ...??

any idea of how to make excel tell me how many days excluding Xday remain as of Today()?

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.



EDIT:

in case someone wants to use these without dependency on other cells:

=SUM(INT((WEEKDAY(TODAY()-{2,3,4,5,6,7})+(EOMONTH(TODAY(),0)-TODAY()))/7))

=SUM(INT((WEEKDAY(TODAY()-{2,3,4,5,6,7})+DAY(EOMONTH(TODAY(),0)))/7))

Originally Posted by icestationzbra

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:

=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

for the variations you need, my suggestions will not work well. @jasonb's formula is recommended for your purposes.

i have updated @jasonb's formula to fit your needs in the attached workbook. you have to decide whether you want to include TODAY's date in day's left or day's gone. accordingly, use the TODAY()+/-1 in the appropriate formula.

thanks,

I'll check it out asap. at work now, getting busy. have a great weekend!

OK.... another lull here...
so, If I want to include today in days remaining, I ad the +1 to the formula in column M (days left)as you have?

Formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()+1&":"&EOMONTH(TODAY(),0))))={2,3,4,5,6,7}))

I see a +1 in N (days gone) also,

Formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&TODAY())))={2,3,4,5,6,7}))

but I honestly can't decipher what all the components of that formula are doing.

what does the ROW do with +1$: eom/today? I gather that the order the EOM and Today() parts appears determine which end of the month it's subtracting from?

try:

in day's left:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&EOMONTH(TODAY(),0))))={2,3,4,5,6,7}))

in day's gone:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&TODAY()-1)))={2,3,4,5,6,7}))

"EOMONTH(TODAY(),-1)+1" translates to "last day of last month + 1" and gives you the first day of the current month.

above formula is @jasonb's; i have just modified it to fit OP's needs.

Those numbers were determined by the question you asked. For example, you know there will always be at least 24 workdays in a month with Sundays off. The 25 was just 24+1 since it helped with some of the followon formulae. 28 is the number of days in a month with no partial weeks (i.e. 4 weeks). Anyway, forget about the one above...

I thought I had posted this, but apparently I didn't hit the 'reply' button. This formula should meet your needs for Sunday off.
=DAY(EOMONTH(A1,0))-DAY(A1)+1-INT((DAY(EOMONTH(A1,0))-DAY(A1)+1)/7)-IF(WEEKDAY(A1,2)-WEEKDAY(EOMONTH(A1,0))<=0,0,1)

The first part (DAY(EOMONTH(A1,0))-DAY(A1)+1) just calculates the number of days remaining in that month.

The next part (-INT((DAY(EOMONTH(A1,0))-DAY(A1)+1)/7)) removes the number of full weeks (since every full week has one Sunday).

The final part (-IF(WEEKDAY(A1,2)-WEEKDAY(EOMONTH(A1,0))<=0,0,1)) subtracts the special case where the remaining days (i.e. a partial week) span across a Sunday. It basically says that if your start day is 'later in the week' than the end of the month, then you must have spanned across a Sunday. There is some trickery there. We know we need to either subtract 1 or nothing.

This formula is 'tuned' to having Sundays off, so if you change the day, or want to add multiple days off, then this needs to be modified and, in some cases, becomes trickier. If you are going to add multiple days, then a UDF using VBA may be the easier route. Or, you could use the first two parts of my formula as a starting point, and then create a table to see how many extra days you need to subtract. It may help to create a table with one column being the weekday of your start day, another column being the weekday of the end of the month, and a final column with the number of days you need to subtract. That last one will need to be calculated by you manually, but then you may see a pattern in that table and you can then create a nice formula to handle it.

yes, props to you both! it's working very well, and your explanation was helpful!

this place rocks