Determine what days fall in which week based on knowing only the Month and Year

[Tyron]

Hey guys,

I was wondering if it were possible (with NO VBA) to have the month and year in two cells and in another 5 cells indicate the days in each week. There is room to have as many helper cells as necessary.

Below find an example of the before and after:
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]October
[/TD]
[TD]2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Week1:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Week2:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Week3:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Week4:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Week5:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]October
[/TD]
[TD]2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Week1:
[/TD]
[TD]09/29-10/03
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Week2:
[/TD]
[TD]10/06-10/10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Week3:
[/TD]
[TD]10/13-10/17
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Week4:
[/TD]
[TD]10/20-10/24
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Week5:
[/TD]
[TD]10/27-10/31
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you can see this basically emulates the calendar on your computer except it only indicates the range between Monday and Friday.

I am open to the VBA resolution if there is no FORMULA only resolution, but am trying to avoid if possible so I don't have to change worksheet into a macro enabled worksheet.

Thanks in advance

Ty

 

[Rick Rothstein]

Try putting this formula in B2...

=(A1&B1)-WEEKDAY(A1&B1)+2+7*(ROW(A1)-1)

Then put this formula in B3 and copy it down...

=B2+7

 

[Rick Rothstein]

Try putting this formula in B2...

=(A1&B1)-WEEKDAY(A1&B1)+2+7*(ROW(A1)-1)

Then put this formula in B3 and copy it down...

=B2+7

I think the above is too simple... try these formulas in the indicated cells instead

B2: =(A1&B1)-WEEKDAY(A1&B1)+2+7*(ROW(A1)-1)

B3: =B2+7

B4: =B3+7

B5: =B4+7

B6: =IF(MONTH(B5)=MONTH(B5+7),B5+7,"")

B7: =IF(B6="","",IF(MONTH(B6)=MONTH(B6+7),B6+7,""))

 

[Tyron]

Hey Rick Rothstein,

Thanks. Both of your answers work good. I see that the second answer you have provided has a spot for Week6. Or should B6 and B7 be shifted up one cell. I ask this for the following reason. If you change the month to indicate March instead of October it allocates the 31st to a sixth week, but in my case I would want it to ignore this day because it would be part of April's first week.

Unexpected problems: I didn't anticipate that excel would strip out the leading zero if it was a month less then 10 and the same thing for days less than 10. I will add an IF to two helper cells to put them back in and see if that helps as well. Format required is 03/03-03/07

Thanks for your help. It answered my question. Could you take a look at the last part of your 2nd answer for me to determine if it needs to be changed since the last day of march would be carried over to the next month?

Thanks again for your help.

Ty

 

[Tyron]

Hey Rick Rothstein,

I didn't understand until now that it was also indicating that the first month of March is 2/24-2/28. I am guessing this happened since the first falls on Saturday. Is there anyway to tell excel to drop these dates and go with 3/3-3/7 as the first week?

I am also figuring my idea of using an IF formula won't be able to help me in the case of adding the preceeding zero to the date as I probably will have to change the format of the date into a text representation first.

thanks in advance

Ty

 

[barry houdini]

Hello Ty,

So I'm assuming from your comments that a week belongs to the month in which the majority of the MF days fall (or alternatively you can define that as the month in which the Wednesday falls)

Try this setup based on Rick's suggestion:

In B2 copied to B5

=TEXT((A$1&B$1)+ROWS(B$2:B2)*7-2-WEEKDAY(4&A$1&B$1),"mm/dd-")&TEXT((A$1&B$1)+ROWS(B$2:B2)*7+2-WEEKDAY(4&A$1&B$1),"mm/dd")

then in B6 only for a possible 5th week

=IF(TEXT((A1&B1)+35-WEEKDAY(4&A1&B1),"mmmm")=A1,TEXT((A1&B1)+33-WEEKDAY(4&A1&B1),"mm/dd-")&TEXT((A1&B1)+37-WEEKDAY(4&A1&B1),"mm/dd"),"")

This assumes you have the full month name in A1 and the year in B1 - the TEXT function gives you dates in the correct format with leading zeroes

 

[Tyron]

Hey barry houdini,

That hits the mark. Great job. One question though. Does this extrapolate the information from the computer's calendar so that I can go forward and backward in time (years)?

later

Ty

 

[barry houdini]

No problem

It uses Excel's calendar, which begins in 1900 so as long as you don't want to go back before then it should work for any Year/Month combination, either in the future or the past.

For the changed cell locations use this formula in H6 copied down to H9

=TEXT((H$3&H$4)+ROWS(H$6:H6)*7-2-WEEKDAY(4&H$3&H$4),"mm/dd-")&TEXT((H$3&H$4)+ROWS(H$6:H6)*7+2-WEEKDAY(4&H$3&H$4),"mm/dd")

and this one in H10

=IF(TEXT((H3&H4)+35-WEEKDAY(4&H3&H4),"mmmm")=H3,TEXT((H3&H4)+33-WEEKDAY(4&H3&H4),"mm/dd-")&TEXT((H3&H4)+37-WEEKDAY(4&H3&H4),"mm/dd"),"")

 

[Tyron]

Hey barry houdini,

What would I need to do to use these formulas in different cells? I have changed A1 to be H3 and B1 to H4 and the cells used for the results are H6 through H11. I was able to successfully change the first 4 weeks, but for some reason after changing the cell references for the 5th week it is just blank.

Thank you in advance.

Ty

 

[Tyron]

Hey barry houdini,

Thanks. I appreciate it. This is a big help in making my worksheet that much more dynamic.

Now I just need to figure out how to move the cell locations around and it still work.

later

Ty

No problem

It uses Excel's calendar, which begins in 1900 so as long as you don't want to go back before then it should work for any Year/Month combination, either in the future or the past