+ Reply to Thread
Results 1 to 4 of 4

Sorting Dates in Days of the Week

  1. #1
    Registered User
    Join Date
    06-13-2005
    Posts
    4

    Sorting Dates in Days of the Week

    Given the following list of dates:

    7/24/2005 12:15
    7/21/2005 13:36
    7/27/2005 15:03
    7/14/2005 15:07
    7/15/2005 16:26
    7/8/2005 11:55

    in custom format: m/d/yyyy h:mm

    I need to be able say there were [the max # of logins in an hour on a single Sunday (as opposed to all Sundays)] happened on [Date] [Hour].

    I need to be able to make that statement for each day of the week. Actual list contains 5100 entries, each entry is a login time.

    In words, I need to be able say on this date, there were 55 logins in the 13:00 hour, and that was the most people logged into a server in an hour on a Sunday in the entire month.

    Thank you so much for your help.
    Last edited by Eagle784; 08-18-2005 at 04:13 PM.

  2. #2
    David McRitchie
    Guest

    Re: Sorting Dates in Days of the Week

    I would try a pivot table after preparing a columns of data as

    A1: OrigData
    B1: Date
    C1: Day
    D1: hour
    E1: Count
    A2: 07/24/2005 12:15
    B2: =TEXT(A2,"yyyy-mm-dd ddd")
    C2: =WEEKDAY(A2)
    D2: =HOUR(A2)
    E2: 1 for each entry in this column

    select B1:E5101 then Data, Pivot Table (you said you had 5100 rows)
    drag Date to drop Row fields here
    drag Hour to drop Column fields here
    drag Count to drop Data fields here

    within the pivot table if it is B5:I12
    then set up Conditional Formatting
    Condition 1: formula is: =AND(B5=MAX($B5:$I5),B5=MAX(B$5:B$12)) Red max row&col
    Condition 2: formula is: =B5=MAX(B$5:B$12) Green max col (hour)
    Condition 3: formula is: =B5=MAX($B5:$I5) Blue max in row (date)


    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Eagle784" <Eagle784.1tz7ie_1124395511.8932@excelforum-nospam.com> wrote in message
    news:Eagle784.1tz7ie_1124395511.8932@excelforum-nospam.com...
    >
    > Given the following list of dates:
    >
    > 7/24/2005 12:15
    > 7/21/2005 13:36
    > 7/27/2005 15:03
    > 7/14/2005 15:07
    > 7/15/2005 16:26
    > 7/8/2005 11:55
    >
    > in custom format: m/d/yyyy h:mm
    >
    > I need to be able say there were [the max # of logins in an hour on a
    > single Sunday] happened on [Date] [Hour].
    >
    > I need to be able to make that statement for each day of the week.
    > Actual list contains 5100 entries, each entry is a login time. Thank
    > you so much for your help.
    >
    >
    > --
    > Eagle784
    > ------------------------------------------------------------------------
    > Eagle784's Profile: http://www.excelforum.com/member.php...o&userid=24261
    > View this thread: http://www.excelforum.com/showthread...hreadid=396973
    >




  3. #3
    Registered User
    Join Date
    06-13-2005
    Posts
    4
    Thanks, but I ended up doing the following:

    Log-In list is in column A (e.g A2:A8), organize your sheet as follows:

    Cell C1: "Hour"; Cell D1: 7/1/2005; Cell E1: 7/2/2005; ...; Cell AH: 7/31/2005

    Cell C2: "0:00"; Cell C3: "1:00"; ...; Cell C25: "23:00": format C2:C25 as "h:mm"

    Cell D2: "=SUMPRODUCT(--(DAY($A$2:$A$8)=DAY(D$1)),--(TIME(HOUR($A$2:$A$8),MINUTE($A$2:$A$8),0)>=$C2),--(TIME(HOUR($A$2:$A$8),MINUTE($A$2:$A$8),0)<=TIME(HOUR($C2),59,59)))"

    Copy Cell D2 -> Paste Range D2:AH25

    select Range D2:AH25 (with Cell D2 active cell) and click Format-> Conditional Format: Select "Formula Is" and enter the following formula:
    "=(D2=MAX(D$2:D$25))"

    It worked great.
    Thanks for your reply, though

  4. #4
    David McRitchie
    Guest

    Re: Sorting Dates in Days of the Week

    I like what you did with formulas instead of a Pivot Table.
    What I was trying to do with the conditional formatting was to
    show the maximum for both row and for column using the
    3 conditional formats available:
    http://www.mvps.org/dmcritchie/excel/condfmt.htm#tables
    for the dimension that you are least interested in, perhaps
    a much lighter color.
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Eagle784" <Eagle784.1u0lid_1124460315.0298@excelforum-nospam.com> wrote in message
    news:Eagle784.1u0lid_1124460315.0298@excelforum-nospam.com...
    >
    > Thanks, but I ended up doing the following:
    >
    > Log-In list is in column A (e.g A2:A8), organize your sheet as follows:
    >
    >
    > Cell C1: "Hour"; Cell D1: 7/1/2005; Cell E1: 7/2/2005; ...; Cell AH:
    > 7/31/2005
    >
    > Cell C2: "0:00"; Cell C3: "1:00"; ...; Cell C25: "23:00": format C2:C25
    > as "h:mm"
    >
    > Cell D2:
    >

    "=SUMPRODUCT(--(DAY($A$2:$A$8)=DAY(D$1)),--(TIME(HOUR($A$2:$A$8),MINUTE($A$2:$A$8),0)>=$C2),--(TIME(HOUR($A$2:$A$8),MINUTE($A$2:$A$8
    ),0)<=TIME(HOUR($C2),59,59)))"
    >
    >
    > Copy Cell D2 -> Paste Range D2:AH25
    >
    > select Range D2:AH25 (with Cell D2 active cell) and click Format->
    > Conditional Format: Select "Formula Is" and enter the following
    > formula:
    > "=(D2=MAX(D$2:D$25))"
    >
    > It worked great.
    > Thanks for your reply, though
    >
    >
    > --
    > Eagle784
    > ------------------------------------------------------------------------
    > Eagle784's Profile: http://www.excelforum.com/member.php...o&userid=24261
    > View this thread: http://www.excelforum.com/showthread...hreadid=396973
    >




+ 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