Forum Discussion
How to repeat date (dd/mm/yyyy) for x amount of rows, then the following day for the next x rows...
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
9 Replies
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.
- Consulting999Copper ContributorHi Hans, thank you for your response; for some reason i am getting an error value when using either of these formulas. The value is written in the date format, what you think the issue may be?