# Sorting Dates in Days of the Week

1. ## 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.

2. ## 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
>

3. 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.

4. ## 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.
>
>
> --
> Eagle784
> ------------------------------------------------------------------------
> Eagle784's Profile: http://www.excelforum.com/member.php...o&userid=24261
>

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

#### 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