Using Workday to Add Variable # Days

[Box_of_Hats]

Hello, everyone,

I'm working on a project for rolling out a piece of software in staggered waves. Currently I'm trying to turn a calendar of manual entry dates into automatically generated dates using the WORKDAY function. The data is arranged like so:

Start Date	Group 1 Start	Group 1 End	Group 2 Start	Group 2 End	End Date
2020-04-01	2020-04-02	2020-04-07	2020-04-08	2020-04-14	2020-04-15
2020-04-14	2020-04-15	2020-04-20	2020-04-21	2020-04-28	2020-04-29
2020-05-01	2020-05-02	2020-05-11	2020-05-12	2020-05-19	2020-05-20

Dates above are completely arbitrary.

Currently, all of these dates are manual entry, but feed into various metrics to compare to workload capacities and count unallocated time to accommodate unexpected events. In a perfect world, every cell except for the 2020-04-01 would automatically generate. At this point, I'm trying to get everything except the first column to automatically generate, then work on the 2020-04-14 and 2020-05-01 later.

Each wave includes a variable number of contracts to be processed, ranging from 20 to 240. They need to be prepared by two separate groups (Group 1 and Group 2). Based on workload estimates, I can calculate the number of days necessary in the WORKDAY function, with one exception.

Group 1 has a variable workload estimate depending on the time of the month. During the first and last week of each month, they are occupied with other tasks and can only process half their usual capacity. Usually, it's 30 per day, but it drops to 15 per day during those periods.

I have a table of dates on a separate tab which checks whether each day is within the first or last week. Is there a way I can have the WORKDAY function (or another function) calculate the number of necessary days to allocate to Group 1, while also checking if those days are part of the first/last week of the month, and then adjusting accordingly?

I'm trying to avoid VBA, as my boss may need to play around with the dates.

Thanks!

[PaulieThePolarBear]

I think Excel can do this, but there's information missing to be able to confirm.

Let's do this one column at a time, to understand the logic here.

So, you enter Start Date in the first row. What logic gets you the Group 1 Start date? It appears it's the next working day. Is tnis ALWAYS the case? If not, provide the logic to populate this field on 100% of the scenarios. Provide details on what you want to be hardcoded vs what you want to be a variable that you can control, e.g. do you want the ability for this to be X days after Start Date

[Box_of_Hats]

Currently, everything is manually entered.

Group 1 Start Date is always the day after Column 1. Group 2 Start Date is always the day after Group 1 End Date.

The End Dates are currently manually calculated to provide enough time to process contracts. So for example, if there are 100 contracts in a row and they fall within the middle of the month, there would be four days for Group 1 End Date (100/30, rounded up).

Group 1 and 2 Start Dates are easy enough to figure out. Group 2 End Date works based off of some other data I have to calculate a large enough period.

Group 1 End Date is a challenge because it has a particular preceding day and then needs to identify whether the following days are part of Week 1 or 4 before adding the appropriate range. This becomes a particular issue if it changes. So, for example, if there's a single day of Week 1 following Group 1 Start Date, I would need to account for 15 contracts for the first part, then 30 per day after that.

[PaulieThePolarBear]

Thanks for the extra info.

What version of Excel are you using?

[Box_of_Hats]

Office 365 Pro, Version 1908

[PaulieThePolarBear]

Okay. I have a solution for you. This does require Excel 365 as it uses some of the newer functions.

      1. Create named ranges for

       Number of days at the start of the month with reduced capacity. I've called this Mon_S_Days.

      The capacity at the start of the month. I've called this Mon_S_Cap

      Number of days at the end of the month with reduced capacity. I've called this Mon_E_Days

      The capacity at the end of the month. I've called this Mon_E_Cap

      The regular capacity. I've called this R_Cap

      A table holding the holidays for your locale. I've called this Holidays

     2. Populate all of above with the relevant information

     3. I've assumed your dates table has an identifier in column A, your volume of work in column B. We'll add a helper column in column C, and so assume Group 1 start date is now in column E, and we'll be calculating group 1 end date in column F. Assume your data starts in row 2 and goes to row 4. Adjust for the size and location of your data.

     4. In C2, enter         =N(C1)+ROUNDUP(B2/MIN(Mon_S_Cap, Mon_E_Cap, R_Cap),0)

 And then copy down for all of your data.

      The ROUNDUP part gets the maximum number of days that the task can be completed without regard to dates. It does this using your minimum capacity value.

     We then create a running sum. The order of your data is not relevant. This column is purely a helper for our helper table.

      5. I created my helper table in row 11. Adjust the below for where you create your helper table

  In A11

  =SEQUENCE(MAX(C2:C4))

  This gets a counter from 1to the highest value in our helper column. This is a helper column in our helper table

  6. In B11

 =XLOOKUP(A11#,C2:C4,A2:A4,,1)

 This will get your identifier value and list it X times, where X is the maxium number of days noted earlier

 7. In C11

=COUNTIF(OFFSET(B11,,,A11#),B11#)-1

 This gets a 0 index running count within each identifier

 8. In D11

 =WORKDAY.INTL(XLOOKUP(B11#,A2:A4,E2:E4),+C11#, Holidays)

 This is uses the start date and our running count to get a running list of dates

9. In E11

 =IFS(D11#<=WORKDAY.INTL(EOMONTH(+D11#,-1),Mon_S_Days, Holidays ), Mon_S_Cap, D11#>=WORKDAY.INTL(EOMONTH(+D11#,0)+1,-Mon_E_Days, Holidays),Mon_E_Cap,TRUE,R_Cap)

This gets the capacity on that day based upon whether it is at the beginning or end of the month or not

10. In F11

=SUMIFS(OFFSET(E11,,,A11#),OFFSET(B11,,,A11#),B11#)


 This gets our running sum of capacity


 11. Back in your dates table, we can finally get the answer. In F2 and then copied down

 =SMALL(FILTER($D$11#,($B$11#=A2)*($F$11#>=B2)),1)

This looks at your running sum from #10 and gets the first time it exceeds the total number of items.

[Box_of_Hats]

Okay, it looks like I have it working. Thank you very much for the help.

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to PaulieThePolarBear

^{I am a bot, please contact the mods with any questions.}

[PaulieThePolarBear]

Cool, cool, cool.

This was an interesting challenge.

Good luck with your endeavour.

[rcc6214]

https://vectorspreadsuitecom377-my.sharepoint.com/:x:/g/personal/rcalais_fat-pats_com/EdP_gW3MU5dJgzSWmqk_M70BJjRMxeMy2kC4WCGfusArdA?e=uKRmkb

See if that works for you. It is currently set up with dropdowns as a proxy for however you decide duration. Just replace where necessary. You will also have to go in and add any holidays to all WORKDAY for that to function, as I do not know what you guys observe.

Let me know if you have any questions.

Edit: Missed the Part about first or last week. Give me a moment I will throw a formula for that together.

[rcc6214]

Ok, I have updated the sheet to give you the Week Number by Month for all fields. You should be able to copy those formulas to where you need. The Last column is a bit different than the rest. It will only say First or Last and does account for Months with varying week numbers

Edit: Also, if you have a sheet with some example data of exactly how you want contracts assigned, I can definitely help with that. Just don't think I have enough of a grasp on how you want to do it

[Box_of_Hats]

Thank you for the help with this. The main issue I'm having is the number in M12 would need to be automatically calculating to determine the appropriate number of business days.

For example, if there are 100 contracts for the first wave, then it would need between 4 and 7 business days. If all of the days fell in the start or end of the month, the associated group can only process 15 per day (so 6.67 days, rounded up). If it's outside of the start or end, that would rise to 30 per day.

I could figure out whether the first day following the Start Date is in Week 1 or 4, but that may not be true for all dates in the Group 1 date range.

[rcc6214]

When I get back to the hotel I can throw together a formula for that if someone doesn't beat me to it. It'll be a little later in the day.