Home |
Search |
Today's Posts |
|
#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] 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 |
#2
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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(H OUR($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 |
#4
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user defined function | Excel Worksheet Functions | |||
Brainteaser about Days Between Dates | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
how do I calculate the days between dates? | Excel Discussion (Misc queries) | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions |