Forum Discussion

Consulting999's avatar
Consulting999
Copper Contributor
Aug 02, 2023

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

  • Consulting999 

    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.

Resources