Formula to generate series of dates

Christine Cao

Does anyone know how to write a formula to have a series of dates generated based on entry of a starting date? In excel, you can enter starting date of 5/1/23, the next cell can can be +7 days so the date is auto generated to be 5/8/23, 5/15/23 etc. I can't figure out how to do this to save time for user since they have to manually enter. Sheet Sample below. I have named each column by WK#. However this column is NOT a date column because if I make it date column, then I cannot simply enter a NUMBER VALUE in that column. In row 2 (RED), I would like a way to have the week date auto generated for those cells ONLY. 5/1/23, 5/8,23, 5/15,23 etc.. Would love to see if anyone has tips or suggestions. Thank you

Kaveri Vipat

Hi Christine Cao 

Hope you doing well 

I have created the formula for the date series. Firstly, you have to enter the starting date in the WK1 column after that you can add these formulas to your columns - 

WK2 column - =[WK1]@row + 7 

Wk3 column - =[WK2]@row + 7 

Wk4 column - =[WK3]@row + 7 

Wk5 column - =[WK4]@row + 7 

Wk6 column - =[WK5]@row + 7 

Wk7 column - =[WK6]@row + 7 

Wk8 column - =[WK7]@row + 7 

Wk9 column - =[WK8]@row + 7 

Wk10 column - =[WK9]@row + 7 

If you want to add some more date series, you can follow these formula series. 

I hope this is useful to you.  

 

Have a Good Day! 

Christine Cao

@Kaveri Vipat Hello Kaveri, Thank you for jumping in to assist me. I used your exact formula in WK2 column cell and all it did was add a "7" to the end resulting in "5/1/237" instead of "5/8/23". See below example screens. Showing you the formula I entered in WK2 cell. NOTE: the column is NOT set as "date" column because I have to be able to enter in a number value in that column as well in the below cells. That was what I found to be a limitation. Is there something else I'm missing?

Kaveri Vipat

Hi Christine Cao 

Hope you doing well, 

Here is the formula for the auto-increment (+7) days in the date, column property is text.  

The formula will work for a particular month and for next month you again have to write the date only and then apply the formula in the next columns so it will again workable for that month. The date format should be only (5/1/23) and if the date will come in two digits you must have to edit the MID of the formula to show the correct output. 

Because we are not using the default date column to get a date, the formula is not able to fetch actual days in a month whether it is 28, 29, 30, or 31. 

Please use this formula - 

WK2 - =LEFT([WK1]@row, 2) + SUM(VALUE(MID([WK1]@row, 3, 1)), 7) + RIGHT([WK1]@row, 3)  

Wk3 - =LEFT([WK2]@row, 2) + SUM(VALUE(MID([WK2]@row, 3, 1)), 7) + RIGHT([WK2]@row, 3)  

Wk4 - =LEFT([WK3]@row, 2) + SUM(VALUE(MID([WK3]@row, 3, 2)), 7) + RIGHT([WK3]@row, 3) 

Wk5 - =LEFT([WK4]@row, 2) + SUM(VALUE(MID([WK4]@row, 3, 2)), 7) + RIGHT([WK4]@row, 3) 

I hope this is useful to you.  

 

Have a Good Day!