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

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 said:

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

Click to expand...

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,""))

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

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

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

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

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"),"")

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

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

barry houdini said:

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

Click to expand...