Excel Formula Help

Question

Excel Formula Help

jklinejr 0

Is there a way to create a formula that automatically returns dates by month, by week, and by year, like the dates below?

Saturday, January 2, 2027

Saturday, January 9, 2027

Saturday, January16, 2027

Saturday, January 23, 2027

Saturday, January 30, 2027

0 comments

3 answers

Your answer

Answer 1

Senthil kumar 1,900

Hi @jklinejr

please add formula in your excel it will be work for you.

=A1 +7 add to your next date row then drag up to you want.

User's image

for the text conversion use below formula.

=TEXT(A1, "dddd, mmmm d, yyyy") this formula will return your desired text format.

User's image

Thanks.

0 comments

Answer 2

Hello @jklinejr

In Excel, you can create formulas that automatically generate recurring dates by week, month, or year from a starting date. I'll explain it in detailed steps:

Generate Every Saturday (Weekly)

1/ Enter the first date in A2: 1/2/2027

User's image

2/In A3, enter: =A2+7

User's image

3/ Press Enter and drag the formula down.

Excel adds 7 days each time, giving you every Saturday.

Note

If you see #### after dragging the formula down, it usually means the column is too narrow to display the date.

You can fix by following below steps:

Widen column A by dragging the column border to the right.
Or double-click the right edge of the column header to AutoFit.

User's image ->

If you get stuck while trying the steps above, please share a screenshot or provide more detailed information. I will check the situation and continue to support you.

Kind regards,

If the answer is helpful, please click "Yes". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 3

Yes. Enter the first date in a cell, then use either Auto Fill or a formula.

For a weekly series like the Saturdays shown:

Type the first date, for example 1/2/2027, in a cell.
Drag the fill handle down.
Select Auto Fill Options and choose Fill Series.
If needed, format the cells as a long date so they display like Saturday, January 2, 2027.

For a formula-based weekly series:

If the first date is in A1, enter this in A2: =A1+7
Copy the formula down to return each following Saturday.

For monthly increments, use the DATE function pattern:

If the starting date is in A1, use: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Copy down to move forward by month.

For yearly increments:

If the starting date is in A1, use: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Copy down to move forward by year.

Excel’s DATE function automatically adjusts when month or day values go past the normal range.