SOLVED

Auto Fill Week and Date From Single Cell

Copper Contributor

Hello, 

 

I've been trying to set up a formula that auto-populates cells with days of the week and a second cell with mm/dd format all based on a single starting date. 

 

For example:

 

Cell A1 sets the date for a series of other cells. A1 reads: 2/1/2024

 

I want this to then autofill a series of cells that are not adjacent to the initial:

H2 should read "02/01"

I2 as "02/02"

J2 as "02/03" 

...and so on. 

 

How can I make this happen without having to edit the starting date in multiple places?

 

Thanks!

 

 

3 Replies
best response confirmed by ScoutLaser (Copper Contributor)
Solution

@ScoutLaser 

 

In H2:

=SEQUENCE(1, 29, A1)

This will spill to 29 columns (if you want more or fewer dates, change the number 29 in the formula)

Select the spill area and format it as mm/dd

Hi @Hans Vogelaar,

 

Thanks for that. Is there a reason that this would not work in a Table set up with column headers? It seems to work in other cells, but not there. I keep getting a readout showing an incorrect date. 

 

Thanks!

@ScoutLaser 

Dynamic array formulas don't work in tables.

Instead, enter the following formula in H2:

=$A$1+COLUMN(H2)-COLUMN($H2)

If necessary, format H2 as a date.

Fill to the right.

1 best response

Accepted Solutions
best response confirmed by ScoutLaser (Copper Contributor)
Solution

@ScoutLaser 

 

In H2:

=SEQUENCE(1, 29, A1)

This will spill to 29 columns (if you want more or fewer dates, change the number 29 in the formula)

Select the spill area and format it as mm/dd

View solution in original post