Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
schedule
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Course Schedule | Excel Discussion (Misc queries) | |||
Schedule Help | Excel Discussion (Misc queries) | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) | |||
Schedule? | Excel Discussion (Misc queries) | |||
Help with schedule | Excel Worksheet Functions |