Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Fill Special-Work Days (2000)

    Anyone out there know if you can fill down or accross with only workdays (i. e., excluding Saturdays, Sundays and Holidays)?
    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    If you put your starting date in A1 and copy down this formula:

    =IF(WEEKDAY(A1)=6,A1+3,A1+1)

    It will return just weekdays. Holidays would be a bit more difficult

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    Thanks a ton!

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Fill Special-Work Days (2000)

    Hi JL,

    I didn't quite understand cpod's answer - I got something but I'm not sure what it was.

    Anyway, another approach would be to create a custom list of just weekdays for Excel 2000 (should work in 97 but didn't test). You can do this a number of ways. Here's one:
    1. With the existing list of all 7 days, type Monday in a cell and then fill this for the next 4 cells down. This will give you Monday-Friday. Select the 5 cells. If you filled a 5th cell, you'd get Saturday. But it ain't over 'til the fat lady sings (or anyone else)
    2. go into Tools | Options. CLick the tab for custom lists. Towards the bottom of the dialog, you'll see a line that says Import list from cells and your selection filled in. Click on import button and you'll have a new list.

    To use this, just type any day of the week in a cell and fill as normal. You will just get weekdays.

    If you want a full 7-day cycle, you'll have to begin it with Saturday or Sunday so Excel picks up the 7-day weekday list. You don't have to use your starting point. But you can't get a 7-day cycle by starting on a weekday because of the new 5-day list.

    Note that you can always delete the 5-day cycle item.

    Fred

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    Fred,
    I am thoroughly confused. I have never worked with lists before, so I am having a problem "getting out of the box", so to speak. Can you walk me through, step-by-step, how to do so, including using your suggestion?
    TYhanks,
    Jeff

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Fill Special-Work Days (2000)

    Jeff,

    No problem. Lists are very helpful.

    1. Create a weekday-only list. To do this, you'll actually use Excel's 7-day list.
    - type "Monday" w/o the quotes in a cell - doesn't matter where.
    - you need to have the next 4 cells have "Tuesday" thru "Friday" w/o quotes (I won't say that again). Doesn't matter if it's vertical or horizontal as long as they're in consecutive cells. To do this, either:
    --- type each day in a consecutive cell; or
    --- click on "Monday" and move your mouse to the lower right corner of the cell where there's a little square. This is the fill square. Your mouse changes to a + sign. Now drag on the square with the + showing until you've filled thru "Friday". If you go past "Friday" (ie, past the 4th cell), it's OK to have Saturday, Sunday and even another Monday. If you're not filling cells with days of the week, you're probably not dragging on the +. Click the undo arrow on the menu and try again.
    - select the 5 cells for Monday thru Friday. Do NOT select Saturday,... if you created more days in the previous step.

    2. Now you have to tell Excel that Monday thru Friday is a list you want it to know about. To do this (note I'm working in Excel 97 here at home but I think this is the same in 2000. above step #1 is the same):
    - click on the Tools menu and go down to options.
    - on the options dialog, choose the "Custom Lists" tab (see attachment for screen shot)
    - the cells you selected at the end of #1 are shown in the "Import list from cells" line towards the bottom of the dialog
    - click the Import button and you're done. You should see in the "Custom Lists" section (upper left of the dialog) the beginning of the list. In the "List entries" section (upper right) you should see all the items in the list.

    Now you're ready to use your new list. Just type any weekday like you did in step #1. Move your mouse to the lower right to get the +. Drag as far as you want to fill however many days you want. When you go past Friday, the next day will be Monday. As I said in my previous post, if you want a 7-day cycle, you now have to type Saturday or Sunday as the first item if you're filling it as we did in #1.

    Hope this helps.

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Fill Special-Work Days (2000)

    Jeff,

    My original post didn't have the attachment. I edited and re-posted it. However, if my explanation w/o he screen shot is sufficient, ignore this.

    Fred

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    Fred,
    Thanks a bunch for your very inciteful tips-where I was missing the boat was in not highlighting on the lower right square and dragging down/accross.
    Anyway, Happy New Year to you and yours!
    Thanks again,
    Jeff

  9. #9
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    Hi there,

    I honestly can't think why no-one else has told you this but...

    If you write Monday in a cell, hover your mouse over the autofill handle (little cross in the bottom right corner of the cell), hold down your right mouse button (don't panic that the mouse pointer changes shape - keetp it held down) and drag across or down the number of cells you wish to fill. When you let go a menu of choices will pop up. One of them says Weekdays. Click on this and you will not get Saturdays or Sundays in your list. Negates the need for a second list.
    Neil Eustice
    Woody Worshipper

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Fill Special-Work Days (2000)

    Thanks a bunch-good tip!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •