Calculating weeks

[Steve]

Hi

I'm hoping this is more straightforward than I'm making it.

What I want is a formula to look at cell A1 (month) and B1 (year), and
give me the first complete week (ending on a Monday) in that month,
and a second formula to give me the last complete week (ending on a
Monday) in that month. So:

A1 B1
November 2011

result: First 07.11.11; Last: 28.11.11

Any ideas would be greatly appreciated.

Alan

 

[Ron Rosenfeld]

Hi

I'm hoping this is more straightforward than I'm making it.

What I want is a formula to look at cell A1 (month) and B1 (year), and
give me the first complete week (ending on a Monday) in that month,
and a second formula to give me the last complete week (ending on a
Monday) in that month. So:

A1 B1
November 2011

result: First 07.11.11; Last: 28.11.11

Any ideas would be greatly appreciated.

Alan

The problem is that your specifications are not clear.

Excel does not "give you" anything. In terms of your question, it can return a date or multiple dates.
If you are looking for it to return multiple dates, it can provide vertical or horizontal arrays, or fill in a sequence of cells in the same row or the same column.

In any event, a starting point for whatever you want would be to determine the date of the first and last Monday in a month.

The first Monday would be given by the formula:

=DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,1)-2)

and the last Monday by the formula:

=DATE(B1,A1+1,1)-WEEKDAY(DATE(B1,A1+1,1)-2)

What to do with these values depends on specifics you have not yet supplied.

 

[Steve]

Hi Ron

Thank you for replying.

The dates would be used to calculate the number of weeks between start
and finish in order to determine weekly Student loan amounts. Even I
can do the formula for that one

Thanks again!

Alan

 

[Ron Rosenfeld]

Hi Ron

Thank you for replying.

The dates would be used to calculate the number of weeks between start
and finish in order to determine weekly Student loan amounts. Even I
can do the formula for that one

Thanks again!

Alan

Well, what I supplied should get you started. Let me know if you need anything more.