Sorting Dates in Days of the Week

[Eagle784]

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.

 

[David McRitchie]

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]

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$25))"

It worked great.
Thanks for your reply, though

 

[David McRitchie]

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.