formula to sort times

[Retiger]

I've a table hours, it is the list of date and time when I've check in the entrance of my office.


It looks like: 13/12/2011 13:43


Some days, it has 4 check in, but others can have just 1 (I've forgotten to check out), or 6, or 5...


I want a formula that gives me the time number that I've checked in the day. For example, the first time of the day, 1, the second 2, etc.


I'm thinking about it. Some idea ?

 

[Hui]

Retiger

Assuming your data is in A2:A100

In B2

=A2-Int(A2)

Copy down

 

[Retiger]

Thanks Hui, I think that I've not explained myself very well...


The list is as follows: (only some rows, the list has around 2000 lines)


DATA HOUR

11/04/12 11/04/2012 8:09

11/04/12 11/04/2012 13:07

11/04/12 11/04/2012 13:57

11/04/12 11/04/2012 19:01

12/04/12 12/04/2012 7:59

12/04/12 12/04/2012 13:38


I want to include 1, 2, 3, 4, 1, 2 in a third column (day 11: 1-2-3-4, day 12: 1-2)


I've tried this formula:

=SUM(IF([@[HOUR]]=LARGE(([HOUR])*([@Data]=[Data]);{123456789101112});{123456789101112};0))


And the result is just the opposite: 4, 3, 2, 1, 2, 1


Sorry, I dont know how to explain better.

 

[Retiger]

here is the file


http://www.2shared.com/file/Eve6UHtB/Book1.html

 

[Hui]

Retiger

What about the following in D5

=IF(B5<>B4,1,D4+1)

or

=IF([@Data]<>B4,1,D4+1)

Copy down

 

[Retiger]

Thanks a lot Hui, it works. I´ve been looking for something that worked even if the table was not sorted, but I think is too complicated.


It´s easy to sort the table and simplify the formula like your proposal !!