start and end date formula...for week wise and for the month

[Ajinkya]

Dear Friends,


Im fail get the week wise week's start and end date in the cell, it should be dependent on the month. for e.g.


Considering for the May-2012


week1 01-05

week2 06-12

week3 13-19

week4 20-26

week5 27-31


Month will be adjustable and on that basis week1, wee2 etc. dates should be change as per calender.


pls help

 

[Luke M]

You might take a look at how Chandoo built his calendar.

http://chandoo.org/wp/2011/12/27/download-free-2012-calendar/


It uses some of the same techniques you would need to determine dates for each week.

 

[NARAYANK991]

Hi Ajinkya ,


You can try the following to see if it is what you are looking for :


1. Assume your reference date ( month entered as a date ) is in A1 ; assume the week ends on a Saturday ( day 7 ).


2. In cell A5 ( just an example , you can choose any other cell ) , enter 1 , since the first day of the first week will always be 1.


3. In cell B5 , enter the following formula as an array formula ( using CTRL SHIFT ENTER ) :

[pre]

=IFERROR(IF(A5="","",MATCH(1,(WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),Day_of_month))=7)*(DAY(DATE(YEAR($A$1),MONTH($A$1),Day_of_month))>=A5),0)),DAY(EOMONTH($A$1,0)))
Copy the above formula downwards in column B as far as you wish , say 6 rows.


4.  In cell A6 , and thereafter in column A ( say the next 6 rows ) , enter the following formula :

=IFERROR(IF(AND(B5+1<=DAY(EOMONTH($A$1,0)),B5+1>=A5),B5+1,""),"")

[/pre]
The formula in (3) above uses the named range Day_of_month referring to the formula :


=ROW(INDIRECT("1:31"))


Narayan