Non calendar months - 1st Monday in Month

[Sayth]

I am trying to use a cell formula to determine weeknum. The first monday of the month is the first week.

I have adapted this formula from here http://www.cpearson.com/excel/WeekNumbers.aspxto this

=TRUNC(((B3-DATE(YEAR(B3),1,1)+MOD((1 = Monday)-WEEKDAY(DATE(YEAR(B3),1,1)),7))+6)/7)

b2 has my day in dd/mm/yyyy format. Can't quite get it to work though, any ideas.

 

[T. Valko]

I am trying to use a cell formula to determine weeknum. The first monday of the month is the first week.

I have adapted this formula from here http://www.cpearson.com/excel/WeekNumbers.aspxto this
=TRUNC(((B3-DATE(YEAR(B3),1,1)+MOD((1 = Monday)-WEEKDAY(DATE(YEAR(B3),1,1)),7))+6)/7)

b2 has my day in dd/mm/yyyy format. Can't quite get it to work though, any ideas.

This will return the 1st Monday date for the month of the date entered in cell A2:

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1-2)

 

[Biz]

This will return the 1st Monday date for the month of the date entered in cell A2:

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1-2)

Mate you like a reservoir of Excel formulas. Where ever you work u must Excel God
Biz

 

[Sayth]

This will return the 1st Monday date for the month of the date entered in cell A2:

=A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1-2)

I need it to count the weeks though. So each week has a number.

Ultimately I want it to come out in format
January
week 1
week 2
week 3
week 4
February # first Monday of next month
week 1 etc.

 

[T. Valko]

Mate you like a reservoir of Excel formulas. Where ever you work u must Excel God
Biz

 

[T. Valko]

I need it to count the weeks though. So each week has a number.

Ultimately I want it to come out in format
January
week 1
week 2
week 3
week 4
February # first Monday of next month
week 1 etc.

Sorry, I don't understand.

 

[Sayth]

Well we do reporting via months and weeks. For each month the first week starts at the first monday of the month.

I have got it so people can select a month to report from ie If they want July they select July.

But what i am trying to nut out is if they want only week 1 & 2 from July or the first 3 weeks.

So if a manager wants to compare the first two weeks of July with the first two weeks of August the user would select

July
-> week 1
-> week 2
# leave week 3 and 4 unchecked and select
August
-> week 1
-> week 2

Does that make sense. i know what i want but maybe i am not saying it clearly

 

[Sayth]

well half the answer is

[B]=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)[/B]

from here http://www.rondebruin.nl/weeknumber.htm

 

[Sal Paradise]

You could be clearer.

For instance:
"With input X in Cell A1, I want Cell A2:A5 display Y." That way we know what information you have, what information you want, and where you want it.

 

[Sayth]

okay in B2 my table has dates dd/mm/yyyy they are for any date gone in this current calendar year, there an no exclusions there will be an entry for every date gone in the year so far and will be updated daily.

We report monthly the start of each month is the first Monday in the month. This sometimes could be the 31st of last month if the Tuesday is the 1st.

We track weekly the results Monday to Sunday.

So the first monday in the year for the first week was 03/01/2011 dd/mm/yyyy.

Using this formula I have assigned each week a value 1,2,3 etc.

=INT((B2-DATE(YEAR(B2-WEEKDAY(B2-1)+4),1,3)+WEEKDAY(DATE(YEAR(B2-WEEKDAY(B2-1)+4),1,3))+5)/7)

But how do I group this into January (week1, week2,week3,week4); February(week1, week2, week3,week4) etc so that I can use the pivot table filter select on these values to choose the first two weeks of january for example.