schedule
With this in A1 of Sheet1:
mar 20-mar26
put this formula in A2:
=TEXT(DATE(2008,INT((SEARCH(LEFT(A1,3),"JanFebMarA prMayJunJulAugSepOctNovDec")
+2)/
3),MID(A1,4,2)+1),"mmmdd")&"-"&TEXT(DATE(2008,INT((SEARCH(MID(A1,7,3),"JanFebMa rAprMayJunJulAugSepOctNovDec")
+2)/3),RIGHT(A1,2)+1),"mmmdd")
(All one formula - be wary of spurious line breaks in the newsgroups).
Copy this formula down to A7 to give you this:
mar20-mar26
Mar21-Mar27
Mar22-Mar28
Mar23-Mar29
Mar24-Mar30
Mar25-Mar31
Mar26-Apr01
Then in A1 of Sheet2 you need to have this formula:
=TEXT(DATE(2008,INT((SEARCH(LEFT(Sheet1!
A1,3),"JanFebMarAprMayJunJulAugSepOctNovDec")+2)/3),MID(Sheet1!
A1,4,2)+7),"mmmdd")&"-"&TEXT(DATE(2008,INT((SEARCH(MID(Sheet1!
A1,7,3),"JanFebMarAprMayJunJulAugSepOctNovDec")+2)/3),RIGHT(Sheet1!
A1,2)+7),"mmmdd")
Note that this is looking back to Sheet1!A1 and adding seven onto the
dates. You need to copy the formulae from Sheet1 A2:A7 into Sheet2
A2:A7 to get the next continuous ranges, so that it will look like
this:
Mar27-Apr02
Mar28-Apr03
Mar29-Apr04
Mar30-Apr05
Mar31-Apr06
Apr01-Apr07
Apr02-Apr08
For subsequent sheets you need to copy the second formula into A1 of
the new sheet, and the other formulae into A2:A7, but change the
formula in A1 as follows:
EITHER change the +7 to +14, +21 etc (twice) and increase the
increment by 7 for each sheet,
OR change the sheet reference from Sheet1 to Sheet2, Sheet3 etc (4
times) and for each subsequent sheet you need to look back to the
previous one.
Hope this helps.
Pete
On Mar 20, 12:24*am, ganga wrote:
i would like to create *a week schedule for my work..
eg:
in sheet 1
mar 20-mar26
mar 21-mar 27
in sheet 2 i want that to be
mar 27- mar 31
mar 22 -apr 01
and keep on adding 7 days in each sheet..
Thanks
|