Determine work days in current month or next month based on day of the month

Originally Posted by sbrnard

I'd like to use an IF statement that says if the formula is zero or negative, calculate the net work days for the next month. The 5 work days from the end of the current month would be added to the total for the next month, but again 5 days would be subtracted from the end of the next month.

Here's is the formula that checks for your requirement (as quoted above) :

Formula:

=IF(((NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY()-1,0),Holidays!A1:A9))-(NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1,Holidays!A1:A9)))<=5,NETWORKDAYS(EOMONTH(TODAY(),0)+1,EOMONTH(TODAY(),1),Holidays!A1:A9)+((NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY()-1,0),Holidays!A1:A9))-(NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1,Holidays!A1:A9))),(NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY()-1,0),Holidays!A1:A9))-(NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1,Holidays!A1:A9)))

Please check and change the conditional limit accordingly in the above formula (highlighted in red) [as <=5 or <5 - refer below note].

Originally Posted by sbrnard

The problem is once I reach the actual last 5 workdays of the month, the formula calculates the remaining number of work days as zero or negative.

Note: I tested this formula and it works correctly.

Just to confirm, Today is the actual last actual working for this month (coincidence ? ). Since only after today you reach the actual last 5 days of this month, should this formula calculate the remaining no. of workdays including those of the next month from today itself or from the next week onwards?

If you need the formula to calculate it from today itself, just leave the condition as <=5, else if it is from next week onwards, then it needs to changed as <5

Originally Posted by sbrnard

You are correct, today (5/22) is the last work day of our May month. So today would equal 1 (I mistakenly stated -5 in my initial post, which would result in 0).

Tomorrow (5/23) starts our June month. There are 21 work days in June. So subtracting the 5 last work days of June and adding the last 5 work days of May would result in an answer of 21 remaining work days for tomorrow. The answer for Tuesday (5/27, since Monday is a holiday) would be 20. And so on...

I hope that makes sense.

Hi,

Subtracting 5 days from June and adding 5 days in May, with this formula would result in just the number 21 again (we can interpret it as days from May or June, but Excel would take it just as a number). I assume you may be tracking your data on a monthly basis. If so, I would like to see the layout of the worksheet that you use (without any data). Could you post a sample workbook (removing all sensitive information)?

It would further help me to understand the context where you use this formula, because I don't want to complicate this date formula any further.

I'm working out the logic, the IF condition which I gave you in post #2 would have to be formulated in a different way.

I need a few clarifications to code this logic for calculating the remaining Workdays. I'm analyzing your worksheet currently, will get back to you with my questions.

Originally Posted by sbrnard

I'd like to use an IF statement that says if the formula is zero or negative, calculate the net work days for the next month. The 5 work days from the end of the current month would be added to the total for the next month, but again 5 days would be subtracted from the end of the next month.

Originally Posted by sbrnard

You are correct, today (5/22) is the last work day of our May month. So today would equal 1 (I mistakenly stated -5 in my initial post, which would result in 0).

Hi Steve,

From your workbook, I can deduce that the lanes 1,2,3 and 5 work independently except the Special lane, which is used to manage the shortfall in any of the lanes after the end of month (i.e. during the actual last week of the month).

If my above assumption is right, then you need the workdays remaining formula to show you the number of workdays reducing on a daily basis from the start of the month.

So, as I said earlier (and from what you stated first and in post #3 - second quote above), the number of workdays remaining - being a reducing number everyday, becomes zero (0) at the last working day of the month (which is 5 days prior to the last working day) and it needs to be calculated accordingly. In other words, it should show the actual number of workdays at the start of a month or during the month (for that month), but the formula should work in a different way during the actual last 5 days (only during the last week) to address the packaging production shortfall. Correct me if I'm wrong here.

In case, if whatever I've understood (so far) is completely different from the actual context or if you do not understand what I've said above, forget about it. Kindly give me a brief explanation on the background information on how you want this workdays remaining to be used in your worksheet. I also want you to fill out the worksheet with mocked up numbers.

Hi Steve,

Find below the formula for calculating the Workdays Remaining :

Formula:

=IF(((NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),Holidays!$A$1:$A$9))-6)>0,((NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),Holidays!$A$1:$A$9))-6),(((NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),Holidays!$A$1:$A$9))-6)+NETWORKDAYS(EOMONTH(TODAY(),0)+1,EOMONTH(TODAY(),1),Holidays!$A$1:$A$9)))

After subtracting the last 5 actual working days in a month (your buffer week), you also need to deduct the current working day, so I've subtracted 1 more day (as highlighted in blue).
Let me know if you have any questions.

Do you care what the result shows on a non-workday? What would you show on a Saturday or Sunday that falls after the last working day of one month and before the first working day of the next month?

If you use this formula the result in that case will be zero, otherwise you get the number of working days left in your month (including today but not the last 5 working days)

=NETWORKDAYS(A2,LOOKUP(A2,WORKDAY(EOMONTH(A2,{-1,0;0,1})+1,-5,holiday_range)),holiday_range)-1

Assumes any date in A2 (e.g today's date)