0

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? enter image description here

4
  • Welcome. Would you please edit your question to clarify a few things: 1-is Mon, 4 Jan 21 the first day of your year? 2 - How are week-by-Month numbers (weeks1-4/5) organised on each sheet?
    – Tedinoz
    Apr 25, 2021 at 2:38
  • I have added a screenshot of part of one page. What I want is to change the date in the top spot and have all the others change.
    – Helen Eld
    Apr 27, 2021 at 17:40
  • @How does the screenshot changes things. You said you wanted to change just the first day of the year and all the dates change or at least change the first one of the month? It is a pity that you didn't include weeks#4 and #5 because that is the $64 question. In order to know the stating date for any given month, you need to know whether the preceding month had 4 weeks or 5 weeks. I wonder if you are looking for a very simple answer where none exists.
    – Tedinoz
    Apr 28, 2021 at 23:12
  • sorry I have not answered your question to help me I have 5 weeks available on each month tab. I am a visual person so the answers I have been given so far make no sens to me, and I am unsure how to change the question to help you give me an answer. I only found this sight the day I posted my question.
    – Helen Eld
    May 4, 2021 at 16:30

1 Answer 1

0

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

Helper


Sample Month template

Sample Month

2
  • @HelenEld You added the screenshot/comment to your question since my answer. If the answer doesn't meet your needs, it would be better to address your comment to the answer. To be honest, I don't see how your screenshot and comment changes the outcome. Would you please explain, how is it that the suggested answer does not meet your needs.
    – Tedinoz
    Apr 28, 2021 at 23:14
  • @HelenEld Let’s ignore template layout for a moment... Do you understand why I’ve suggested a helper sheet? Do you agree that the starting date in any given month depends on whether the preceding month had 4weeks or 5weeks? (Like March, for instance, is a 5 week month). I could just change the template layout, but I’m concerned that this won’t necessarily make things any more clear for you. Let me know about whether you understand why the helper sheet is necessary.
    – Tedinoz
    May 4, 2021 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.