 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / May 2008Fill Series by Date Won't Work
Version: v.X Operating System: Mac OS X 10.3 (Panther) Processor: Power PC My group meets the third Wednesday of every month. I'd like to create a list of future meeeting dates using the fill series function. In the first cell (formatted M/D/Y) I enter the date of the this month's meeting (4/16/08). In the cell directly under that, I enter next month's meeting date (5/21/08). I select both, then press Control while dragging the handle. No matter what I choose from the popup contextual menu (Day, Weekday, whatever) I can't get the series to fill projected dates for the rest of the year. Help! Lolamoth@officeformac.com wrote on 2008-04-11 21:17: > Version: v.X > Operating System: Mac OS X 10.3 (Panther) [quoted text clipped - 7 lines] > choose from the popup contextual menu (Day, Weekday, whatever) I can't get the > series to fill projected dates for the rest of the year. Help! Assuming the first date is in cell A1, insert this formula in cell A2 and copy down. =DATE(YEAR(A1);MONTH(A1)+1;22-WEEKDAY(DATE(YEAR(A1);MONTH(A1)+1;4))) For other days of the week than Wednesday, replace the last 4 in the formula with: Thursday: 3 Friday: 2 Saturday: 1 Sunday: 7 Monday: 6 Tuesday: 5 JL Mac OS X 10.4.11, Office v.X 10.1.9 Thanks for the formula. I must not be doing some important step because still no results. Here's what I did: Formatted all cells in the worksheet to Date style 0/00/00. Typed this month's meeting date in cell A1: 4/16/08 Clicked on A2, then chose Insert > Function. I couldn't paste until I'd chosen a function, so I chose Date & Time > DATE, which inserts "=Date()" into the formula window. I clicked inside the () and was presented with a dialog with three separate date windows. Didn't know what to put in therenever have understood that damn box. Clicked out of it. Ignored the error message. I pasted the formula into the (). The result was: =DATE(=DATE((YEAR(A1);MONTH(A1)+1;22-WEEKDAY(DATE(YEAR(A1);MONTH(A1)+1;4))))) Of course with the duplicate "=DATE" I got an error. I removed one of them but still got an error. I removed extra parens ")" at the end, one at a time. No luck. The last step you mentioned was "copy down." Does that mean drag the fill handle? Sorry to be so thick. Im not new to Excel but Im not too savvy about functions. suzanne wrote on 2008-04-17 10:02: > Thanks for the formula. I must not be doing some important step because still > no results. [quoted text clipped - 26 lines] > Sorry to be so thick. Im not new to Excel but Im not too savvy about > functions. First I have to apologize, the formula should have been =DATE(YEAR(A1),MONTH(A1)+1,22-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4))) with commas instead of semi-colons between arguments (my OS X is set to French, hence the difference). Secondly, to insert the formula in your workbook, proceed this way. Select it her in the message (the whole line), and Copy. Click on the cell where you need the formula (probably the cell below the first date), and Paste. If you first date is not in A1, replace all A1 with the correct cell reference. Copy down, yes with the fill handle. Hope this helps. JL Mac OS X 10.4.11, Office v.X 10.1.9 Thank you for your detailed and helpful instructions! I'm sorry, but it didn't work. Here's what I did: 1. Selected all cells and formatted them 0/00/00. 2. Selected A1 and inserted 6/19/08. 3. Selected your formula =DATE(YEAR(A1),MONTH(A1)+1,22-WEEKDAY(DATE(YEAR(A1),MONTH(A1) and chose Copy. 4. Selected A2 and chose Paste. The formula remained in A2 as a text string. Was there something I missed? Thanks, S :worried: :worried: Realizing you probably meant to insert the formula into the Formula Bar, I pasted it there and got a message the formula had an error. I'd appreciate any ideas you might have about this. Thanks, S Try: A1: 6/18/2008 A2: =DATE(YEAR(A1),MONTH(A1)+1, 1+((3 - (4 >= WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))))*7) + (4 - WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)))) Where the 4 corresponds to Wednesday (Sunday = 1) and the 3 means the 3rd Wednesday of the month. > Thank you for your detailed and helpful instructions! I'm sorry, but it > didn't work. Here's what I did: [quoted text clipped - 13 lines] > Thanks, > S :worried: :worried: |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.