Rank Date and Time

[putzhead]

I'm trying to rank date and time where the rank formula will continue to the next day. I have Dates in column A, Times in column B. Column C is showing the results I would like to have. Can someone help with a rank formula? TIA


Date Time Rank
04/01/13 9:30 2
04/01/13 9:00 1
04/01/13 14:00 4
04/03/13 10:00 14
04/01/13 15:00 5
04/01/13 15:30 6
04/01/13 17:00 7
04/01/13 18:00 8
04/02/13 11:00 9
04/01/13 10:00 3
04/02/13 13:00 11
04/02/13 14:00 12
04/02/13 12:00 10
04/03/13 9:00 13

 

[Matt Rogers]

Hello.

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='7' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='91,5pt'><col width='60pt'><col width='60pt'><col width='84,75pt'><col width='14,25pt'><col width='95,25pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >Date</td><td align='middle' >Time</td><td align='middle' >Your rank</td><td align='middle' >Formula rank</td><td align='right' > </td><td align='middle' >HelperColumn</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >04/01/13</td><td align='middle' >9:30</td><td align='middle' >2</td><td align='middle' >2</td><td align='right' > </td><td align='middle' >41365.01649</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' >04/01/13</td><td align='middle' >9:00</td><td align='middle' >1</td><td align='middle' >1</td><td align='right' > </td><td align='middle' >41365.01563</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' >04/01/13</td><td align='middle' >14:00</td><td align='middle' >4</td><td align='middle' >4</td><td align='right' > </td><td align='middle' >41365.02431</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' >04/03/13</td><td align='middle' >10:00</td><td align='middle' >14</td><td align='middle' >14</td><td align='right' > </td><td align='middle' >41367.01736</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' >04/01/13</td><td align='middle' >15:00</td><td align='middle' >5</td><td align='middle' >5</td><td align='right' > </td><td align='middle' >41365.02604</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' >04/01/13</td><td align='middle' >15:30</td><td align='middle' >6</td><td align='middle' >6</td><td align='right' > </td><td align='middle' >41365.02691</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' >04/01/13</td><td align='middle' >17:00</td><td align='middle' >7</td><td align='middle' >7</td><td align='right' > </td><td align='middle' >41365.02951</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' >04/01/13</td><td align='middle' >18:00</td><td align='middle' >8</td><td align='middle' >8</td><td align='right' > </td><td align='middle' >41365.03125</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' >04/02/13</td><td align='middle' >11:00</td><td align='middle' >9</td><td align='middle' >9</td><td align='right' > </td><td align='middle' >41366.01910</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' >04/01/13</td><td align='middle' >10:00</td><td align='middle' >3</td><td align='middle' >3</td><td align='right' > </td><td align='middle' >41365.01736</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' >04/02/13</td><td align='middle' >13:00</td><td align='middle' >11</td><td align='middle' >11</td><td align='right' > </td><td align='middle' >41366.02257</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' >04/02/13</td><td align='middle' >14:00</td><td align='middle' >12</td><td align='middle' >12</td><td align='right' > </td><td align='middle' >41366.02431</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' >04/02/13</td><td align='middle' >12:00</td><td align='middle' >10</td><td align='middle' >10</td><td align='right' > </td><td align='middle' >41366.02083</td></tr><tr><td style='background-color:#cacaca' align='middle'>15</td><td align='middle' >04/03/13</td><td align='middle' >9:00</td><td align='middle' >13</td><td align='middle' >13</td><td align='right' > </td><td align='middle' >41367.01563</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>D2</td><td><Span style='color:#222222'>=RANK</Span><Span style='color:#0000DD'>(F2,F$2:F$15,1)</Span><Span style='color:#222222'></Span></td></tr><tr><td>F2</td><td><Span style='color:#222222'>=A2+B2/24</Span></td></tr></table>

 

[Matt Rogers]

Ten years later ...

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='5' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='91,5pt'><col width='60pt'><col width='73,5pt'><col width='88,5pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='5'>Worksheet 'Sheet2'</td></tr><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >Date</td><td align='middle' >Time</td><td align='middle' >Rank</td><td align='middle' >Range</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >04/01/13</td><td align='middle' >9:30</td><td align='middle' >2</td><td align='middle' >Row 2 to 99</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' >04/01/13</td><td align='middle' >9:00</td><td align='middle' >1</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' >04/01/13</td><td align='middle' >14:00</td><td align='middle' >4</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' >04/03/13</td><td align='middle' >10:00</td><td align='middle' >15</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' >04/01/13</td><td align='middle' >15:00</td><td align='middle' >5</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' >04/01/13</td><td align='middle' >15:30</td><td align='middle' >6</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' >04/01/13</td><td align='middle' >17:00</td><td align='middle' >7</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' >04/01/13</td><td align='middle' >18:00</td><td align='middle' >8</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' >04/02/13</td><td align='middle' >11:00</td><td align='middle' >9</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' >04/01/13</td><td align='middle' >10:00</td><td align='middle' >3</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' >04/02/13</td><td align='middle' >13:00</td><td align='middle' >12</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' >04/02/13</td><td align='middle' >14:00</td><td align='middle' >13</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' >04/02/13</td><td align='middle' >12:00</td><td align='middle' >11</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>15</td><td align='middle' >04/03/13</td><td align='middle' >9:00</td><td align='middle' >14</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>16</td><td align='middle' >04/02/13</td><td align='middle' >11:30</td><td align='middle' >10</td><td align='right' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td><Span style='color:#222222'>=SUMPRODUCT</Span><Span style='color:#0000DD'>(--</Span><Span style='color:#222222'>(A2+B2>A$2:A$99+B$2:B$99)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>-COUNTIF</Span><Span style='color:#0000DD'>(A$2:A$99,"")</Span><Span style='color:#222222'>+1</Span></td></tr></table>