Automatic updation of dates

[ashish navale]

Hello,


I have a report in which I get data for weekly production. In this sheet there is heading for weeks of month. eg. In 1st column lets say on A4 cell it looks like "1st Week (2 Jan - 6 Jan)"

Then on B4 "2nd Week (9 Jan - 13 Jan)" and so on..So my requirement is as below:

1. I want a formula which can automatically update the week dates inside the brackets montly. Like if now its Feb month the those dates should automatically change to as below..

1 Feb - 3 Feb

6 Feb - 10 Feb

13 Feb - 17 Feb and so on...here one thing is important. If 1st day of month falls on Sunday then it should catch automatically the 2nd day. Also when the month ends,the 1st day of next month if it falls on day which is not Monday...then the date should be shown from that start date till Fridays date..the best example of this is 1st week of Feb..Plz exclude Saturdays and Sundays...


Hope this information is sufficient for you efficient and excellent people..Plz help with my query...this will reduce my manual effort

 

[ashish navale]

Hi..Need help for automatic updation of dates

 

[NARAYANK991]

Hi Ashish ,


Can you check out the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21138


Please do not click on the hyperlink ; copy the entire address and paste it in your browser.


Narayan

 

[NARAYANK991]

Hi Ashish ,


I noticed a mistake in the earlier upload ; can you ignore that and check out the following link ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21139


Narayan

 

[SirJB7]

Hi, ashish navale!

You can also try this:

a) A2: 01/02/2012

b) B2 (dummy cell, just for copying same formula in C2): =A2-3

c) C2: =IF(MONTH(B2+3)=MONTH($A2),B2+3,"")

d) D2: =IF(C2<>"",IF(MONTH(C2+7-WEEKDAY(C2,2)-2)=MONTH($A2),C2+7-WEEKDAY(C2,2)-2,EOMONTH(C2,0)),"")

e) Copy C2:D2 to E2:N2 (six possible weeks)

Regards!