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

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

I use the following formula to determine the remaining number of work days in the month -
``Please Login or Register  to view this content.``
. In order to meet certain deadlines, our "working" end of the month is 5 work days prior to the actual end of the month. So I use the same formula and subtract 5. 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. 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.

2. ## Re: 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:
`Please Login or Register  to view this content.`
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

3. ## Re: Determine work days in current month or next month based on day of the month

Sarang -

Thank you for your help. The answer you provided is on the right track, but I may not have explained the results I'm looking for clearly.

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.

4. ## Re: Determine work days in current month or next month based on day of the 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).

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.

5. ## Re: Determine work days in current month or next month based on day of the month

Workbook is attached.

Ignore the blue, orange and pink cells. The work day calculation is in N43. That value is used in N44 and subsequently J20 & N20. The user enters the volume for the day in J35:44 and N35:39. The required output to meet the volume requirements is shown in J11:19 and N11:19. J20 and N20 let the user know if there is enough capacity to meet the demand.

As you can see the value for today in N43 is -1 because May ended yesterday and today is the start of June. The number should be 21, representing the work days in June (subtracting the last 5 work days in June but adding the last 5 work days in May).

6. ## Re: Determine work days in current month or next month based on day of the month

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.

7. ## Re: 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.
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.

8. ## Re: Determine work days in current month or next month based on day of the month

Sarang -

Lanes 1-5 are totally independent of each other. The workload for lanes 1, 2 & 5 is expected to be completed by the end of each day, so the number of work days in the month does not affect them. Lanes 3 & 4 have a large workload that needs to be completed by the end of the month. So I use the work days in the month to determine if we have enough capacity to finish the work by month end.

So your second sentence is correct - the workdays remaining formula shows the number of workdays in the month and reduces by 1 each day. The work days remaining on last day of our month (5 days before actual month end) would be 1. The next day would start a new month and the work days remaining would equal the number of work days in the next month.

So my original formula results in a negative number for the last 5 days of the calendar month. I'd like for the formula to calculate the work days for the next month starting in the last 5 work days of the current month. So the remaining number of work days for today is 20. It is calculated as follows - 21 work days in June minus the last 5 work days in June plus the remaining 4 work days in May.

9. ## Re: Determine work days in current month or next month based on day of the month

Hi Steve,

Find below the formula for calculating the Workdays Remaining :
Formula:
`Please Login or Register  to view this content.`
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.

10. ## Re: Determine work days in current month or next month based on day of the month

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)

11. ## Re: Determine work days in current month or next month based on day of the month

Sarang -

I believe the formula provided meets all the requirements. Thanks for all of your help! I really appreciate it.

12. ## Re: Determine work days in current month or next month based on day of the month

Non-workdays are "bonus" days and don't need to be accounted for in the worksheet.

Thanks for the formula you provided. I will take a look at it and see if I can incorporate it into the worksheet.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1