+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

    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. #2
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by sbrnard View Post
    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: copy to clipboard
    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].

    Quote Originally Posted by sbrnard View Post
    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
    Last edited by Saarang84; 05-22-2014 at 10:20 PM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  3. #3
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

    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. #4
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by sbrnard View Post
    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.
    Last edited by Saarang84; 05-23-2014 at 06:51 AM.

  5. #5
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

    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).
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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.
    Last edited by Saarang84; 05-23-2014 at 10:56 AM.

  7. #7
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

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

    Quote Originally Posted by sbrnard View Post
    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.
    Quote Originally Posted by sbrnard View Post
    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.
    Last edited by Saarang84; 05-24-2014 at 06:04 AM.

  8. #8
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

    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. #9
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    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: copy to clipboard
    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. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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)
    Audere est facere

  11. #11
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

    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. #12
    Registered User
    Join Date
    04-05-2013
    Location
    Gurnee, IL
    MS-Off Ver
    MS365 (PC) Version 2310
    Posts
    51

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

    daddylonglegs -

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM
  2. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2013, 08:45 AM
  3. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2013, 07:18 AM
  4. [SOLVED] Help with a system to auto populate month names based on current month
    By rosboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2012, 05:17 PM
  5. Replies: 4
    Last Post: 08-09-2006, 09:59 AM

Bookmarks

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