time_report_date-wise_employee-wise

  • Thread starter Thread starter via135
  • Start date Start date
V

via135

I am having date-wise, employee-wise login logoff data in Col A to C as under

DATE EMP_NO TIME

01/03/2014 1525336 09:04
01/03/2014 1525336 09:04
01/03/2014 1525336 09:27
01/03/2014 1525336 09:41
01/03/2014 1525336 09:58
01/03/2014 1525336 10:06
01/03/2014 1525948 10:14
01/03/2014 1525948 10:14
01/03/2014 1525948 10:22
01/03/2014 1525948 10:47
03/03/2014 1525336 10:13
03/03/2014 1525336 10:53
03/03/2014 1525336 10:59
03/03/2014 1525336 11:04
03/03/2014 1525336 11:19
03/03/2014 1525336 11:36
03/03/2014 1525948 10:16
03/03/2014 1525948 10:17
03/03/2014 1525948 10:17
03/03/2014 1525948 10:18
03/03/2014 1525948 10:18
03/03/2014 1525948 10:37
04/03/2014 1525336 10:09
04/03/2014 1525336 10:13
04/03/2014 1525336 10:23
04/03/2014 1525336 10:43
04/03/2014 1525948 10:12
04/03/2014 1525948 10:13
04/03/2014 1525948 10:14
04/03/2014 1525948 10:14
04/03/2014 1525948 10:20

What I want is date-wise, employee-wise login (starting time) logoff (ending time) report for each of the employees as example given for the above list as under;

DATE EMP_NO LOG_IN LOG_OUT
01-03-2014 1525336 09:04 10:06
01-03-2014 1525948 10:14 10:47
03-03-2014 1525336 10:13 11:36
03-03-2014 1525948 10:16 10:37
04-03-2014 1525336 10:09 10:43
04-03-2014 1525948 10:12 10:20

Help please.

Regards.

-via135
 
Hi,

Am Sun, 9 Mar 2014 11:05:52 -0700 (PDT) schrieb via135:
I am having date-wise, employee-wise login logoff data in Col A to C as under

DATE EMP_NO TIME

01/03/2014 1525336 09:04
01/03/2014 1525336 09:04
Click to expand...
What I want is date-wise, employee-wise login (starting time) logoff (ending time) report for each of the employees as example given for the above list as under;

DATE EMP_NO LOG_IN LOG_OUT
01-03-2014 1525336 09:04 10:06
01-03-2014 1525948 10:14 10:47
Click to expand...

your data in Sheet1. Then in Sheet2 C2:
=MIN(IF(Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100=A2&B2,Sheet1!$C$2:$C$100))
and in D2:
=MAX(IF(Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100=A2&B2,Sheet1!$C$2:$C$100))
Enter both array formulas with CTRL+Shift+Enter and copy down


Regards
Claus B.
 
I am having date-wise, employee-wise login logoff data in Col A to C as under



DATE EMP_NO TIME



01/03/2014 1525336 09:04

01/03/2014 1525336 09:04

01/03/2014 1525336 09:27

01/03/2014 1525336 09:41

01/03/2014 1525336 09:58

01/03/2014 1525336 10:06

01/03/2014 1525948 10:14

01/03/2014 1525948 10:14

01/03/2014 1525948 10:22

01/03/2014 1525948 10:47

03/03/2014 1525336 10:13

03/03/2014 1525336 10:53

03/03/2014 1525336 10:59

03/03/2014 1525336 11:04

03/03/2014 1525336 11:19

03/03/2014 1525336 11:36

03/03/2014 1525948 10:16

03/03/2014 1525948 10:17

03/03/2014 1525948 10:17

03/03/2014 1525948 10:18

03/03/2014 1525948 10:18

03/03/2014 1525948 10:37

04/03/2014 1525336 10:09

04/03/2014 1525336 10:13

04/03/2014 1525336 10:23

04/03/2014 1525336 10:43

04/03/2014 1525948 10:12

04/03/2014 1525948 10:13

04/03/2014 1525948 10:14

04/03/2014 1525948 10:14

04/03/2014 1525948 10:20



What I want is date-wise, employee-wise login (starting time) logoff (ending time) report for each of the employees as example given for the above list as under;



DATE EMP_NO LOG_IN LOG_OUT

01-03-2014 1525336 09:04 10:06

01-03-2014 1525948 10:14 10:47

03-03-2014 1525336 10:13 11:36

03-03-2014 1525948 10:16 10:37

04-03-2014 1525336 10:09 10:43

04-03-2014 1525948 10:12 10:20



Help please.



Regards.



-via135
Click to expand...



Hi Claus,

I am getting value 0 in all the cells.!

-via135
 
In message <[email protected]> of Mon, 10 Mar 2014 07:39:23
in microsoft.public.excel.worksheet.functions, Claus Busch
Hi,

Am Sun, 9 Mar 2014 19:38:34 -0700 (PDT) schrieb via135:


check the format of your times
Have a look:
https://onedrive.live.com/?cid=9378AAB6121822A3&id=9378AAB6121822A3!3
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
for workbook "Time_Report"
Click to expand...

Claus,
I did so. I am surprised columns 1 and 2 of sheet 2 are literals, rather
than formulae. I also got 0 on column 3 - I guess because I had not set
1 & 2.
 
Hi Walter,

Am Mon, 10 Mar 2014 07:16:27 +0000 schrieb Walter Briscoe:
I did so. I am surprised columns 1 and 2 of sheet 2 are literals, rather
than formulae. I also got 0 on column 3 - I guess because I had not set
1 & 2.
Click to expand...

is it working now?
You also get 0 if the spelling in Sheet1 and Sheet2 differs because you
have leading or trailing spaces. Run TextToColumns over each column to
delete these spaces.


Regards
Claus B.
 
Hi Walter,



Am Mon, 10 Mar 2014 07:16:27 +0000 schrieb Walter Briscoe:








is it working now?

You also get 0 if the spelling in Sheet1 and Sheet2 differs because you

have leading or trailing spaces. Run TextToColumns over each column to

delete these spaces.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Yes, Mr Claus,

You are correct. After removing the trailing/leading spaces in Col B, it works like a charm..!!!
Thanks and regards..!

-via135
 
Back
Top