Forum Discussion

Edith_Thornburg's avatar
Edith_Thornburg
Copper Contributor
Apr 26, 2022
Solved

continuing a series question

I have a table with 6 columns for each month, each month alternates either blue or white. I am trying to automate the sequence so that I don't have to type it in each month. I am sure it is an easy f...
excel
Formulas and Functions
  • amit_bhola's avatar
    amit_bhola
    Apr 26, 2022

    Edith_Thornburg , see attached file for example.

     

    Set-up the formula as below.

     

    Formula trend is :-

     

    If Start is from column A, then

    =DATE(2022,FLOOR((COLUMN()-1)/6,1)+3,1)

     If Start is from column B, then

    =DATE(2022,FLOOR((COLUMN()-1-1)/6,1)+3,1)

    If Start is from column C, then

    =DATE(2022,FLOOR((COLUMN()-1-2)/6,1)+3,1)

    and so on...

     

    Once setup, you can drag the formula to the right.

     

    Format the cell as below setting :-

     

    Set up 12 columns manually for color and formula.

    Then select 12 columns, and drag to the right.

    The formula and color shall be filled as you desire.

     

     

     

     

     

amit_bhola's avatar
amit_bhola
Iron Contributor
Apr 26, 2022

Edith_Thornburg , see attached file for example.

 

Set-up the formula as below.

 

Formula trend is :-

 

If Start is from column A, then

=DATE(2022,FLOOR((COLUMN()-1)/6,1)+3,1)

 If Start is from column B, then

=DATE(2022,FLOOR((COLUMN()-1-1)/6,1)+3,1)

If Start is from column C, then

=DATE(2022,FLOOR((COLUMN()-1-2)/6,1)+3,1)

and so on...

 

Once setup, you can drag the formula to the right.

 

Format the cell as below setting :-

 

Set up 12 columns manually for color and formula.

Then select 12 columns, and drag to the right.

The formula and color shall be filled as you desire.

 

 

 

 

 

Edith_Thornburg's avatar
Edith_Thornburg
Copper Contributor
Apr 26, 2022
I had to tweak it a little, but it worked great! Thank you!

Resources