Formula to get specific results

[DeanJGrey]

Hi!

I am trying to come up with a formula to calculate the starting day of each week in a month.

Here is the case:
I will use this month (October, 2019) for specifics.

There are 31 days in the month. For this exercise, a week starts on a Saturday, but the first week will start on a Tuesday, since that is the first day of the month.

Week 1 - 1(Tue), 2, 3, 4

Week 2 - 5(Sat), 6, 7, 8, 9, 10, 11

Week 3 - 12(Sat), 13, 14, 15, 16, 17, 18

Week 4 - 19(Sat), 20, 21, 22, 23, 24, 25

Week 5 - 26(Sat), 27, 28, 29, 30, 31

My question is, how would I calculate the first day of each week?

I hope I have provided enough information. If not, I'll absolutely provide more.

Thank you, in advance!

 

[Dr.Peterson]

What is the context of the question? What kind of formula do you need (e.g. for a program, a spreadsheet, ...)? What inputs do you want to give the formula? What form should the output take (e.g. 0 for Saturday, or the word "Saturday")?

And who says weeks start on Saturday? (That is, is it a given in your context, or an input?)

 

[DeanJGrey]

It's for a spreadsheet.
I guess the inputs would be the amount of days in the month, the week # that is to be selected (eg. Week 1, 2, 3, etc.).
The output should be the # day that is the first day of the week you want selected (eg. Week 1 = 1, Week 2 = 5, Week 3 = 12, etc.).
It's a given that Saturday is the first day of each week, except for the first week when the 1st of the month is not a Saturday.

Thank you.
Ask for more details if needed
Trying to be as helpful as possible

 

[Dr.Peterson]

Okay.

First, I see I misunderstood the output, so I'm glad I asked. You don't want what day of the week is the first day of a given week (almost always Saturday), but what day of the month is the first day of the week (increasing by 7 after week 2).

So you want to be able to refer to (a) a cell that contains the length of the month (which Excel or equivalent can tell you if you just provide the month itself), and (b) a cell that contains the week number. Don't you also need something from which to determine what day of the week the month starts on? That could be a date ("October 2019") from which Excel could tell you the day of the week, or explicit information about the day. In application, what will you actually know?

I'm not sure you need to provide the length of the month, unless you want error checking to make sure the week number is valid.

Without working out the details yet, it looks like the output for week 1 will always be 1, and for subsequent weeks it will essentially be the number of days from the first day-of-week until Saturday, plus 7 days per week.

Given 31 days in the month, week #3, and the fact that the month starts on Tuesday, the formula will give 12. Is that right?

 

[DeanJGrey]

First, I see I misunderstood the output, so I'm glad I asked. You don't want what day of the week is the first day of a given week (almost always Saturday), but what day of the month is the first day of the week (increasing by 7 after week 2).

Absolutely Correct.

Without working out the details yet, it looks like the output for week 1 will always be 1, and for subsequent weeks it will essentially be the number of days from the first day-of-week until Saturday, plus 7 days per week.

Also correct.

Given 31 days in the month, week #3, and the fact that the month starts on Tuesday, the formula will give 12. Is that right?

Correct as well.

One piece of info that I forgot is that I have the day of the week that is the first day of the month as an input, and a # assigned to it to help with offsets.
Sat: 0
Sun: 1
Mon: 2
Tue: 3
Wed: 4
Thu: 5
Fri: 6

Just can't figure out the formula

 

[Dr.Peterson]

With the first day of the month in B2 and the week number in B3, I have

=IF(B3=1,1,7*B3-B2-6)​

Does that do what you want (except for checking whether it is a valid day, by comparing the output to the number of days in the month)?