Forum Discussion

Ewa_el's avatar
Ewa_el
Copper Contributor
Dec 30, 2022

Autofille of dates and text

Hi, 

I have to create file with dates for each business day of year, but each date has to be multiplicated 10 times (e.g. 10 rows with 2nd of Jan, 10 rows with 3rd of Jan). How I can do this?

 

Aditionally I have ten rows of text and I wanna repeat all these 10 rows for each date. Any better idea than copy and paste? 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 30, 2022

    Ewa_el 

    =IF(MOD(ROW(),10)=0,QUOTIENT(ROW(),10)+44928,QUOTIENT(ROW(),10)+44929)

    You can try this formula in cell C1. The values in column C are formatted as short date.

    =INDEX($A$1:$A$10,IF(ROUNDDOWN(MOD(ROW(),10),0)=0,10,ROUNDDOWN(MOD(ROW(),10),0)))

    You can try this formula in cell D1. The texts are in range A1:A10 in this example.

     

Resources