Best way to change dates in Google Sheets in all the tabs?

Question

I have a sheet that is for accountability in my business, I have each month with it's own tab with five week sections and the dates are done by a Monday thru Sunday sections with the date that is lands on in the month with the start of the month not always landing in the first so the last week of the month data makes sense. For example April 2021 starts on 4/5/2021 and ends on 5/2/2021 due to how the days of the week land. Is there a way to change just the first day of the year and all the dates change or at least change the first one of the month?

Answer 1

You have 12 sheets for each month of the year. Each sheet has columns for the weeks in that month, each week running from Monday to Sunday. Updating monthly and weekly dates is a time-consuming task. You want to know whether there is a way that the year's start date can be nominated, and the weekly and monthly dates will automatically be updated.

The following answer uses a HELPER sheet for listing the weekly dates and their relevant month, and identifying which date is the first date for any given month. This data is then queried to create a 12 row list of the starting date for each month, the month number and the number of weeks in the month.

A sample "Month template" shows how values will update automatically depending on the month number entered.

HELPER SHEET (refer sample data)

Starting date:

• Enter the year's starting date in cell A2

Weekly Dates (Column A):

• Manually populate the weekly dates in Column A:

  Cell A6: =A2, Cell A7: =A6+7, Cell A8-A57: copy formula from cell A7

Month (Column B):

• Cell B6: =month(A6), Cell B7-B57: copy the formula from Cell B6

Unique/Duplicate (Column C):

• Cell C6: =IF(COUNTIF($B$6:$B6,B6)=1, "Unique", "Duplicate"), Cell C7-C57: copy the formula from Cell C6

QUERY analysis(Column D/E/F)

• Cell D5: =query({query({A6:C},"select Col1 where Col3 = 'Unique' label Col1 'Month Start'"), query({B6:B},"select Col1, count(Col1) where Col1 is not null group by Col1 label Col1 'Month', count(Col1) 'Num of Weeks in month'")}, "select Col1, Col2, Col3")

---

MONTHLY template

This describes how to create the sheet for Month#1; sheets for other months can be cloned from this.

• Cell B1: Enter a month number (1-January; 2-February, etc)
• Cell B2: =vlookup(B1,{HELPER!F3:F14, HELPER!E3:E14},2,false) (format as "MMMM YYYY"). This looks up the month number in HELPER QUERYand returns the date value.
• Cell B3: =vlookup(B1,HELPER!F3:G14,2,false). Looks up the month number and returns the value for number of weeks in the month.

Week#s - row 6

• Cell B6/E6: Enter "Week#1", "Week#2", "Week#3", "Week#4"
• Cell F6: =if(B3=5,"Week#5",)

Dates - row 7

• Cell B7: =B2
• Cell C7: =B7+7
• Cell D7/E7: copy the formula from Cell C7
• Cell F7: =if(B3=5,E7+7,)

---

Helper sheet - sample data

---

Sample Month template