+ Reply to Thread
Results 1 to 14 of 14

need to convert list of dates to count no. of dates by week

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    need to convert list of dates to count no. of dates by week

    Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.

    What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc

    I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.

    im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.

    thanks

  2. #2
    Bob Phillips
    Guest

    Re: need to convert list of dates to count no. of dates by week

    =SUMPRODUCT(--(A2:A200>=--"2006-01-04"),--(A2:A200<=--"2006-01-10"))

    gives a count for the week in those dates

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "neowok" <neowok.22as00_1138372203.4775@excelforum-nospam.com> wrote in
    message news:neowok.22as00_1138372203.4775@excelforum-nospam.com...
    >
    > Basically I have 2 columns each with a list of dates in no particular
    > order (and containing blank cells too), one planned date column and one
    > actual date column.
    >
    > What I need to do is plot this on a graph, and since the number of
    > dates has no set limit and I dont want to have to plot maybe 100 dates
    > on the x axis, so i want to group them by week before plotting them,
    > i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th
    > jan etc
    >
    > I have a pivot table that counts how many of each date occurs, i.e. 10
    > x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
    >
    > im sure theres an easyish way of doing it so i can get the 2 lines on
    > the graph for no. of planned dates each week and no. of actual dates
    > each week, i just cant see it.
    >
    > thanks
    >
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile:

    http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716
    >




  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    hmmm that would work if i have a smallish, set number of weeks and i know what the weeks are, but this is a dynamic document where the date range is subject to change daily

  4. #4
    Dave Peterson
    Guest

    Re: need to convert list of dates to count no. of dates by week

    Is the date a row field?

    How about rightclicking on it and choosing choose Group and show detail.

    Then group by weeks.

    If excel's weeks are different than yours, you could use add a new column in the
    raw data and determine the week based on whatever rules you follow. Then use
    that in the pivottable.



    neowok wrote:
    >
    > Basically I have 2 columns each with a list of dates in no particular
    > order (and containing blank cells too), one planned date column and one
    > actual date column.
    >
    > What I need to do is plot this on a graph, and since the number of
    > dates has no set limit and I dont want to have to plot maybe 100 dates
    > on the x axis, so i want to group them by week before plotting them,
    > i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th
    > jan etc
    >
    > I have a pivot table that counts how many of each date occurs, i.e. 10
    > x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
    >
    > im sure theres an easyish way of doing it so i can get the 2 lines on
    > the graph for no. of planned dates each week and no. of actual dates
    > each week, i just cant see it.
    >
    > thanks
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716


    --

    Dave Peterson

  5. #5
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    the dates are in 2 columns, i went to group but cant see how i could group them by week (no options for this)

    top few cells of one of the planned date column and 2 extra needed for the graph may look something like this if it all works

    Planned week ending numberof items
    01-01-06 01-01-06 3
    <blank cell> 08-01-06 2
    <blank cell> 15-01-06 1
    05-01-06 26-02-06 1
    01-01-06
    01-01-06
    25-02-06
    <blank cell>
    <blank cell>
    15-01-06
    03-01-06
    <blank cell>
    etc etc

    the graph could then be based on the week ending and number of items column for the planned series, and then the same thing again for the actual series.

    I think i need to get an automated split of weeks in one column (B) (ending on say sundays) and then in column C, the number of dates in column A which falls during the week in column B

    if not that then some other way of getting the same sort of result just so I can draw the graph.

  6. #6
    Dave Peterson
    Guest

    Re: need to convert list of dates to count no. of dates by week

    If your field has text or blanks, then you can't use that grouping.

    Excel has a =weeknum() function that might be ok right out of the box.

    =if(a2="","",weeknum(a2))
    ???


    neowok wrote:
    >
    > the dates are in 2 columns, i went to group but cant see how i could
    > group them by week (no options for this)
    >
    > top few cells of one of the planned date column and 2 extra needed for
    > the graph may look something like this if it all works
    >
    > Planned week ending numberof items
    > 01-01-06 01-01-06 3
    > <blank cell> 08-01-06 2
    > <blank cell> 15-01-06 1
    > 05-01-06 26-02-06 1
    > 01-01-06
    > 01-01-06
    > 25-02-06
    > <blank cell>
    > <blank cell>
    > 15-01-06
    > 03-01-06
    > <blank cell>
    > etc etc
    >
    > the graph could then be based on the week ending and number of items
    > column for the planned series, and then the same thing again for the
    > actual series.
    >
    > I think i need to get an automated split of weeks in one column (B)
    > (ending on say sundays) and then in column C, the number of dates in
    > column A which falls during the week in column B
    >
    > if not that then some other way of getting the same sort of result just
    > so I can draw the graph.
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716


    --

    Dave Peterson

  7. #7
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    hm might be able to use severla more columns and use weeknum. i can use a macro to copy the weeknum formula down for however many rows the dates go, then maybe make a pivot table of that column giving me a count of each weeknum, then somehow convert the weeknums into weeks that i can use as x axis labels and base the chart off the pivottable but without using a pivotchart. or something, but thats probly a bit too complicated to work hehe ill have to fiddle with it on monday

  8. #8
    Dave Peterson
    Guest

    Re: need to convert list of dates to count no. of dates by week

    Or instead of using the week number, use the date of the first day in the week
    (or the Friday of that week or...)

    neowok wrote:
    >
    > hm might be able to use severla more columns and use weeknum. i can use
    > a macro to copy the weeknum formula down for however many rows the
    > dates go, then maybe make a pivot table of that column giving me a
    > count of each weeknum, then somehow convert the weeknums into weeks
    > that i can use as x axis labels and base the chart off the pivottable
    > but without using a pivotchart. or something, but thats probly a bit
    > too complicated to work hehe ill have to fiddle with it on monday
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716


    --

    Dave Peterson

  9. #9
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    *edit* i dont have the weeknum function available here so cant use that (its not installed and i have no way of installing it)

    I've now used this formula in a dummy column to convert those dates so that they are all on the friday of the given week

    =IF(TEXT(B3,"ddd")="mon",B3+4,IF(TEXT(B3,"ddd")="tue",B3+3,IF(TEXT(B3,"ddd")="wed",B3+2,IF(TEXT(B3,"ddd")="thu",B3+1,IF(TEXT(B3,"ddd")="sat",B3+6,IF(TEXT(B3,"ddd")="sun",B3+5,B3))))))

    now i just need to convert that formula so that i can put it into the macro which copies it down for however many dates there are and point the pivot table at that row
    Last edited by neowok; 01-30-2006 at 05:49 AM.

  10. #10
    Bob Phillips
    Guest

    Re: need to convert list of dates to count no. of dates by week

    =A1+(6-WEEKDAY(A1))

    gets the Friday the date in A1

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "neowok" <neowok.22fxey_1138612501.649@excelforum-nospam.com> wrote in
    message news:neowok.22fxey_1138612501.649@excelforum-nospam.com...
    >
    > hmmm i think that would work better. how would i get the date of the
    > friday in that week? because i would need those dates anyway as the
    > axis labels.
    >
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile:

    http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716
    >




  11. #11
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    ok ive got 2 columns populated with planned dates and actual dates, all converted to the friday of each week so i can get a count of how many were planned/actual in a given week.

    the major problem i have now is trying to plot the 2 series on the line graph. to get the counts of each date ive had to use 2 pivot tables, one that counts the dates in planned column and one counting the dates in the actual column.

    2 problems with this are, i cant plot a graph from 2 different pivot tables, and even if i could, the ranges of weeks do not match, i.e. the 2 pivot tables show

    HTML Code: 
    planned     count              actual         count
    6 jan 06       6               18 nov 05      24
    20 jan 06      2               25 nov 05      13
    27 jan 06      2               2 dec 05       37
    3 feb 06       1               9 dec 05       12
                                   16 dec 05      12
                                   23 dec 05      10
                                   20 jan 06       2
    so as you can see, i somehow need to group up the dates from both planned and actual to use as the X axis catagories, and then plot the 2 series under the correct dates
    Last edited by neowok; 01-30-2006 at 06:25 AM.

  12. #12
    Dave Peterson
    Guest

    Re: need to convert list of dates to count no. of dates by week

    I'm not sure if this fits, but if you made the planned/actual category a column
    field in the pivottable, would it work ok?

    neowok wrote:
    >
    > ok ive got 2 columns populated with planned dates and actual dates, all
    > converted to the friday of each week so i can get a count of how many
    > were planned/actual in a given week.
    >
    > the major problem i have now is trying to plot the 2 series on the line
    > graph. to get the counts of each date ive had to use 2 pivot tables,
    > one that counts the dates in planned column and one counting the dates
    > in the actual column.
    >
    > 2 problems with this are, i cant plot a graph from 2 different pivot
    > tables, and even if i could, the ranges of weeks do not match, i.e. the
    > 2 pivot tables show
    >
    > planned count actual count
    > 6 jan 06 6 18 nov 05 24
    > 20 jan 06 2 25 nov 05 13
    > 27 jan 06 2 2 dec 05 37
    > 3 feb 06 1 9 dec 05 12
    > 16 dec 05 12
    > 23 dec 05 10
    > 20 jan 06 2
    >
    > so as you can see, i somehow need to group up the dates from both
    > planned and actual to use as the X axis catagories, and then plot the 2
    > series under the correct dates
    >
    > --
    > neowok
    > ------------------------------------------------------------------------
    > neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
    > View this thread: http://www.excelforum.com/showthread...hreadid=505716


    --

    Dave Peterson

  13. #13
    Dave Peterson
    Guest

    Re: need to convert list of dates to count no. of dates by week

    And depending on what should happen to the Saturday following that Friday:
    =A1+6-MOD(A1,7)



    Bob Phillips wrote:
    >
    > =A1+(6-WEEKDAY(A1))
    >
    > gets the Friday the date in A1
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "neowok" <neowok.22fxey_1138612501.649@excelforum-nospam.com> wrote in
    > message news:neowok.22fxey_1138612501.649@excelforum-nospam.com...
    > >
    > > hmmm i think that would work better. how would i get the date of the
    > > friday in that week? because i would need those dates anyway as the
    > > axis labels.
    > >
    > >
    > > --
    > > neowok
    > > ------------------------------------------------------------------------
    > > neowok's Profile:

    > http://www.excelforum.com/member.php...fo&userid=5940
    > > View this thread: http://www.excelforum.com/showthread...hreadid=505716
    > >


    --

    Dave Peterson

  14. #14
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154
    thanks for replies.

    i think i have a solution that works now using a few dummy columns with various formulas in to reduce it down to the required data for the graph, did it without using any pivot tables as i justcouldnt get it to format it the right way.

+ 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