Aug 02 2023 12:31 AM
Feels simple enough, I have an excel file with employee data, and want to assign each employee an assessment date. There are alot of employees (around 6000). I want every 210 employees to have the same assessment date, then the following 210 employees to have the next day for their assessment (ideally using Egyptian business days). Is there a way to do this quickly?
Side note: the 6000 employees are also divided by department, so the number used above (210) is only used for the larger departments. Smaller departments will have smaller batches per assessment days and each department has its own sheet in the workbook. In the aggregate sheet, can I filter by department and assign assessment dates?
Assume the dates are to be put in column K
Aug 02 2023 01:23 AM
Enter the first assessment date in K2.
In K3, enter the formula
=WORKDAY.INTL(K2,IF(MOD(ROW(K3),210)=2,1,0),7)
210 is the number of times to repeat - you can change this for other departments.
7 specifies Friday and Saturday as weekend days. If you have a list of public holidays, you can use
=WORKDAY.INTL(K2,IF(MOD(ROW(K3),210)=2,1,0),7,HolidayRange)
Fill down to the last used row.
Aug 02 2023 05:27 AM
Aug 02 2023 07:29 AM
See the attached demo workbook.
Aug 03 2023 12:03 AM
Aug 03 2023 02:14 AM
You only need to change the value 210 to for example 70 or 100.
Aug 03 2023 05:01 AM
Aug 03 2023 05:21 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Aug 03 2023 05:27 AM
@Hans Vogelaar You will see I highlighted the August 20th dates, because that's the first time the formula appears in the sheet (the previous dates were added manually because they didn't match the 75 rows, they are all less than that so doing it manually for the first couple of days was easier). August 20th dates were obtained using the formula, but there are only 25 of them, then the remaining ones until the end of the sheet all respect the 75 employees rule. Hope that makes sense
Aug 03 2023 05:41 AM
The formula that I posted assumed that the first formula would be in row 3. It works differently if the first formula is lower down.
The original formula had MOD(ROW(K3,210)=2 because you want to go to the next date in row 2, 212, 422, 632 etc.
Now, you want to go to the next date on row 127, 202, 277, 349 etc. Those row numbers leave 52 when divided by 75: 127 = 75+52, 202 = 2*75+52, etc. So the formula in D128 should be
=WORKDAY.INTL(D127,IF(MOD(ROW(D128),75)=52,1,0),16)