Early Login, Last Logout

[junoon]

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

 

[Guest]

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]

hi Duke,

Thanks for your reply. However data reports are only created in Excel
& 2ndly i donot know Access.

Is there a possible worksheet function to sum up a day's DateTimestamp
based on an Employee's StatusID, Login=1 & Logout=2.

Note, Sometimes, either one of the 2 may be missing for a particular
day. (See table above.)

Cheers!

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

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!

 

[Guest]

If you fiddle around with your data - in particular adding a column with
formulas that return JUST the date portion of your date time value, then you
can probably get a Pivot table to give you what you need.

No matter what, you have lots of work to do in Excel.

To create the additional column of data, format the column as Date, then use
this formula:

=integer(cell with date time value)

That way you can group your data by date while preseving the original
datestamps

hi Duke,

Thanks for your reply. However data reports are only created in Excel
& 2ndly i donot know Access.

Is there a possible worksheet function to sum up a day's DateTimestamp
based on an Employee's StatusID, Login=1 & Logout=2.

Note, Sometimes, either one of the 2 may be missing for a particular
day. (See table above.)

Cheers!

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

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!