+ Reply to Thread
Results 1 to 5 of 5

How to Create a Chart using days of week, from a series of dates

  1. #1
    Registered User
    Join Date
    06-14-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    27

    Question How to Create a Chart using days of week, from a series of dates

    So I have a column of dates (mm/dd/yyyy) numeric format (needs to stay in this format for other formulas and functions being used.)

    The sheet has over 1000 entries, over several months, and I am trying to create a graph, that shows how many entries we input on which days of the week.

    My Date Column is A.
    Some days have 0 entries, some have 20+.

    I'd like to end up with graphs showing as a %, what day of the week Monday - Sunday, we have out entries.

    Any advice would be helpful.

    Cheers
    Last edited by Dan86; 01-29-2013 at 12:10 AM. Reason: SOLVED

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,328

    Re: How to Create a Chart using days of week, from a series of dates

    Hi Dan.

    Without seeing what you are working with, give this a shot. In a helper column, enter this, copied down (I used C, so adjust the references in the countif() function as needed)...
    =WEEKDAY(A1,2) this will give you the days of the week, with Mon = 1 and Sun=7

    Then make up a small table with 2 columns. In the 1st (I used E, adjust as needed) enter 1 to 7, and in the 2nd, use this, copied down...
    =COUNTIF($C$1:$C$44,E1)

    You can then (if you want) add weekday names, and then chart based on the table

    Let me know how you make out?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-14-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: How to Create a Chart using days of week, from a series of dates

    Thanks FDibbins, works like a charm!

    Is is possible to put the =WEEKDAY(A1,2) into a formula? remove the need for a helper column?

    I'm using COUNTIFS for many things in sorting the mass amounts of data.

    so right now my COUNTIFS has 3 criteria, criteria_range1 being my helper column, and criteria1 being my days of the week (1-7 respectively)

    Is there a way for me to turn criteria_range1 from the helper column into the "=WEEKDAY(A1,2)" code?

    If not is this is working, so no real need to worry.
    Thanks!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,328

    Re: How to Create a Chart using days of week, from a series of dates

    I tried that, and couldnt get it to work

  5. #5
    Registered User
    Join Date
    06-14-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: How to Create a Chart using days of week, from a series of dates

    Thanks anyways FDibbins! It works anyways, I was just trying to remove the need for a helper column, but I'll use it and just Hide it.

    SOLVED

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1