Function for specific weekly dates

Occasional Visitor

I'm trying to create a column that is dated as such:

January, Monday 4 - Sunday 10

January, Monday 11 - Sunday 17

where only the weeks start at Monday. I think this has something to do with the IF function? But I'm a complete beginner and do not understand that function.


Currently I have 



=TEXT(A6, "mmmm, ")&TEXT(H6,"mmm d")&IF(H6<>""," - "&TEXT(I6,"mmm d"),"") 



where A6 is the full date: Monday, January 4, 2021, H6 is the week and date: Monday 4, and I6 is the week and date: Sunday 10.


But, the output for what I currently have is:

January, Monday 4 - Sunday 10
January, Tuesday 5 - Monday 11. 

This is not what I'm looking for. Instead, I need a column of dates only starting on Monday. Is someone able to draft a formula for this? And if need for explanation, please explain it for beginners. 

2 Replies


If in H6 start day when in A6


formula like

=TEXT($H$6 + (ROW()-ROW($H$6))*7, "mmmm, dddd d") &
  " - " &
  TEXT($H$6 + (ROW()-ROW($H$6))*7+6, "mmmm, dddd d")

and drag it down.


You don't really need H6 and I6 for this.

I'd do the following:

Enter the start date January 4, 2021 in cell A6. It doesn't matter how you format it.

In A7, enter the formula =A6+7 and fill or copy this down as far as you want.

In another cell in row 6, enter the formula


=TEXT(A6,"mmmm, dddd d")&" - "&TEXT(A6+6,"dddd d")


Fill or copy down.