Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Excel and dates  (Read 1857 times)

0 Members and 1 Guest are viewing this topic.

Spoiler

    Topic Starter


    Specialist

    Thanked: 50
  • Experience: Beginner
  • OS: Windows XP
Excel and dates
« on: December 14, 2012, 10:10:41 AM »
Hi All,
I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....

12/3/2012
12/4/2012
12/5/2012
12/6/2012
12/7/2012
12/10/2012

etc.....

For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend.

I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it.

Anyone have any ideas?

Thanks
Spoiler

Whenever I watch TV and I see those poor starving kids all over the world, I can't help but cry. I mean I would love to be skinny like that, but not with all those flies and death and stuff." - Mariah Carey, Pop Singer

soybean



    Genius
  • The first soybean ever to learn the computer.
  • Thanked: 469
  • Computer: Specs
  • Experience: Experienced
  • OS: Windows 10
Re: Excel and dates
« Reply #1 on: December 14, 2012, 12:13:35 PM »
I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it.
Are dates for all rows part of the existing data?

If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

oldun



    Intermediate

    Thanked: 30
    • Experience: Beginner
    • OS: Windows 7
    Re: Excel and dates
    « Reply #2 on: December 14, 2012, 04:52:31 PM »
    One option is, with a start date in A1, enter =WEEKDAY(A1,1) in B1, and copy it down.
    Oldun

    Spoiler

      Topic Starter


      Specialist

      Thanked: 50
    • Experience: Beginner
    • OS: Windows XP
    Re: Excel and dates
    « Reply #3 on: December 17, 2012, 08:23:58 AM »
    soybean
    All the dates are is a list. They don't come from any other data. I am using it just to keep track of the days work.

    May this snippet will help explan.



    [year+ old attachment deleted by admin]
    Whenever I watch TV and I see those poor starving kids all over the world, I can't help but cry. I mean I would love to be skinny like that, but not with all those flies and death and stuff." - Mariah Carey, Pop Singer

    soybean



      Genius
    • The first soybean ever to learn the computer.
    • Thanked: 469
    • Computer: Specs
    • Experience: Experienced
    • OS: Windows 10
    Re: Excel and dates
    « Reply #4 on: December 17, 2012, 12:03:27 PM »
    So, somehow, you entered a formula in the top cell of a range and then dragged the content of that cell down to numerous rows below it and the dates filled in those cells but this did not include weekends.  Is that right?  To reiterate, you did not simply enter the date in the first/top cell, you entered a formula which skipped Saturdays and Sundays.  Am I right so far?   

    Spoiler

      Topic Starter


      Specialist

      Thanked: 50
    • Experience: Beginner
    • OS: Windows XP
    Re: Excel and dates
    « Reply #5 on: December 17, 2012, 02:43:04 PM »
    I think you are on the right track. At this point all I am interested in is to list the weekdays. How its done really isn't that important.

    Whenever I watch TV and I see those poor starving kids all over the world, I can't help but cry. I mean I would love to be skinny like that, but not with all those flies and death and stuff." - Mariah Carey, Pop Singer

    BC_Programmer


      Mastermind
    • Typing is no substitute for thinking.
    • Thanked: 1099
      • Yes
      • Yes
      • BC-Programming.com
    • Certifications: List
    • Computer: Specs
    • Experience: Beginner
    • OS: Windows 8
    Re: Excel and dates
    « Reply #6 on: December 17, 2012, 02:54:40 PM »
    this functionality appears to be built into Excel.

    Put the start date in a cell.

    Grab the Fill Handle (it's a small black box in the bottom right of the cell) and drag it down as far as desired. When you release, a small toolbox button should appear, titled "Autofill options". Expand this, and you can select a "Fill weekdays" option, which will exclude weekends.
    I was trying to dereference Null Pointers before it was cool.