How to find out log In log Out Time

[sdsurzh]

Hello Excel Hero,


I have a time sheet which looks like

Date Start Time Stop Time Time Taken

24/07/2012 13:53 15:32 01:39

24/07/2012 15:32 17:54 02:21

25/07/2012 11:41 15:29 03:47

25/07/2012 15:29 16:57 01:27

25/07/2012 16:57 18:06 01:08

26/07/2012 11:47 12:07 00:20

26/07/2012 12:08 14:59 02:50

26/07/2012 14:59 17:54 02:54

26/07/2012 17:54 18:35 00:40


From the above format i want to find out In & Time of each day like below.


Date In Time Out Time

24/07/2012 13:53 17:54

25/07/2012 11:41 18:06

26/07/2012 11:47 18:35


Please help me out


Thanks,

Suresh Kumar S

 

[NARAYANK991]

Hi Suresh ,


Let us assume your data is laid out as follows , starting from A1 :

[pre]

24/07/2012	13:53:00	15:32:00
24/07/2012	15:32:00	17:54:00
25/07/2012	11:41:00	15:29:00
25/07/2012	15:29:00	16:57:00
25/07/2012	16:57:00	18:06:00
26/07/2012	11:47:00	12:07:00
26/07/2012	12:08:00	14:59:00
26/07/2012	14:59:00	17:54:00
26/07/2012	17:54:00	18:35:00
.
.
24/07/2012	13:53:00	17:54:00
25/07/2012	11:41:00	18:06:00
26/07/2012	11:47:00	18:35:00

[/pre]
where the lower output starts from A12.


The results in cells B12 through C14 are from the following formula in column B :


=SMALL(($B$1:$C$9)*($A$1:$A$9=A12),2*(COUNTIF($A$1:$A$9,"<>"&A12))+1)


in cell B12 , entered as an array formula , using CTRL SHIFT ENTER. Copy this downwards , in column B.


In column C , put in the following formula , starting from C12 , and copied downwards :


=MAX(($B$1:$C$9)*($A$1:$A$9=A12))


again entered as an array formula.


If you can be sure that the times in column B will always be less than the corresponding times in column C , then you can replace the above formulae with the following :


=SMALL(($B$1:$B$9)*($A$1:$A$9=A12),(COUNTIF($A$1:$A$9,"<>"&A12))+1)


=MAX(($C$1:$C$9)*($A$1:$A$9=A12))


where the ranges are within one column.


Narayan

 

[sdsurzh]

Thank you very much narayan....... you have saved my precious time.

Once again a BIG Thank to you.


If possible can you explain the below formula

=SMALL(($B$1:$B$9)*($A$1:$A$9=A12),(COUNTIF($A$1:$A$9,"<>"&A12))+1)

 

[NARAYANK991]

Hi Suresh ,


What we are doing by the following construct ,


($B$1:$B$9)*($A$1:$A$9=A12)


entering the whole formula as an array formula , is that we are creating an array of values ; ($A$1:$A$9=A12) creates an array of TRUE / FALSE ( 1/0 ) values ; TRUE or 1 where the value is equal to A12 , and FALSE or 0 where the value is not equal to A12. An example would be :


{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}


When we multiply this array by the array ($B$1:$B$9) , we get another array :


{0.578472222222222;0.647222222222222;0;0;0;0;0;0;0}


where the first two values are the result of multiplying the values in B1 by the first value , TRUE or 1 , and B2 by the second value , also TRUE or 1 , from the earlier array. The 0s are because all the remaining values in the earlier array are FALSE or 0.


From the above array , we wish to select the smallest value , which is 0.57847222


This we do by seeing how many of the array values are 0 , and taking the next value ; in the above example , 7 values are 0 ; if we take the 8th smallest value , we will get 0.57847222


The COUNTIF function returns the number of values , from the given range , $A$1:$A$9 , which satisfy the given criterion , <>A12. The quotes ( " ) and the ampersand character ( & ) are required for the right syntax. The criterion <>A12 is the opposite of the criterion =A12 used in arriving at the array of values.


Narayan

 

[sdsurzh]

thank you very much dude...