time_report_date-wise_employee-wise

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
 
C

Claus Busch

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.
 
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
Click to expand...



Hi Claus,

I am getting value 0 in all the cells.!

-via135
 
W

Walter Briscoe

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.
 
C

Claus Busch

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.
 
V

via135

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtracting time...again 2
Reverse Index Match 2
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Problem formatting dates from CSV file 2
Lookup and count all cells that fall between 2 dates 3
Returning a Date with Multiple Criteria Lookup Values 0
How to find the correct end date 3
How do I count the number of blank cells 0

Top