Calculate First IN and Last Out from datetime col.

[SDC4684]

Dear,

how to get the First IN and Last out for a date from a column having arroung 80 dates (datetime). as i need to calculate the time spent inf office (Last OUt - First In)

please help....


like....


A1 (Col)

04/01/2013 07:36:55 (some time for a particular date there are more than 4 entries will be there)

04/01/2013 16:24:31

04/02/2013 07:16:39

04/02/2013 16:01:05

and so on....

 

[Hui]

SDC4684


Firstly, Welcome to the Chandoo.org forums


Remembering that dates/times are purely numbers we need just find the smallest and largest numbers where a date matches a reference date


Assuming your data went from A2:A13

and you had a reference date at E1


First: =SMALL(IF(INT(A2:A13)=E1,(A2:A13),MAX(A2:A13)),1)

Last: =LARGE(IF(INT(A2:A13)=E1,(A2:A13),MIN(A2:A13)),1)


Should help you


Refer a sample here: https://www.dropbox.com/s/1mdkx0f5r26om6z/SDC4684%20Sample.xlsx

 

[SDC4684]

thanks Hui for your quick response...