time_report_date-wise_employee-wise
 

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

time_report_date-wise_employee-wise
 

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

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

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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

time_report_date-wise_employee-wise
 

On Sunday, 9 March 2014 23:35:52 UTC+5:30, via135 wrote:
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 Claus,

I am getting value 0 in all the cells.!

-via135

time_report_date-wise_employee-wise
 

Hi,

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

I am getting value 0 in all the cells.!

check the format of your times
Have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for workbook "Time_Report"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

time_report_date-wise_employee-wise
 

In message of Mon, 10 Mar 2014 07:39:23
in microsoft.public.excel.worksheet.functions, Claus Busch
writes
Hi,

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

I am getting value 0 in all the cells.!

check the format of your times
Have a look:
https://onedrive.live.com/?cid=9378A...B6121822A3%213
26#cid=9378AAB6121822A3&id=9378AAB6121822A3%213 26
for workbook "Time_Report"


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.
--
Walter Briscoe

time_report_date-wise_employee-wise
 

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.

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

time_report_date-wise_employee-wise
 

On Monday, 10 March 2014 12:53:24 UTC+5:30, Claus Busch wrote:
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.



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

Yes, Mr Claus,

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

-via135