This fairly screams to be in a database, not in Excel. Microsoft has free
downloads of SQL Server 2005 Express. If you use that, load your data into a
table called Newsgroup, this query returns exactly what you want for the min
Login and max Logout for each day


SELECT
EmployeeID,
StatusCode,
CONVERT(nvarchar(15), [timestamp], 101) AS WorkDays,
CASE StatusCode
WHEN 1 THEN Min(TimeStamp)
ELSE Max(timeStamp)
end
from
dbo.Newsgroup
group by
EmployeeID,
StatusCode,
CONVERT(nvarchar(15), [timestamp], 101)
order by
EmployeeID, 3


"junoon" wrote:

Hi all,

I have a timesheet dump which has a Column for EmpCode viz
0,1,2,3,4,5, etc..
Next column is for StatusID where status means 1 = Login & 2 = Logout.
Next Column is for Date & Time Stamp (Long format).

Since the Login & Logout Date& TimeStamp lie in a single column, i
want to calculate, for each EmpID, the earliest time he logged in &
last time he logged out for the SAME day....

Also, if someone knows a way to find if the respective dateTime falls
on a weekend or holiday...

if anyone has an idea using Worksheet function or VBA code to
accomplish this, please let me know....

EmployeeCode StatusID Time
-------------------------------------------------------------------
0 1 4/13/07 7:01 PM
0 1 4/16/07 1:03 PM
0 1 4/16/07 1:05 PM
1 1 4/2/07 4:03 PM
1 2 4/2/07 6:20 PM
1 1 4/5/07 10:14 AM
1 2 4/5/07 1:27 PM
1 1 4/5/07 5:52 PM
1 1 4/17/07 6:03 PM
1 2 4/17/07 6:39 PM
1 1 4/19/07 9:38 AM
1 2 4/19/07 6:02 PM
1 1 4/19/07 7:33 PM
1 2 4/19/07 8:12 PM
1 1 4/19/07 9:05 PM
1 2 4/19/07 10:10 PM
1 1 4/24/07 11:17 AM

Sheet2:
EmpCode Early Login Early Logout
---------------------------------------------------------------



Cheers!