Forum Discussion
Function for specific weekly dates
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
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.
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.
