Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can recalculation be set to "row wise" or column wise" ? | Excel Discussion (Misc queries) | |||
row wise query | New Users to Excel | |||
row wise query | Excel Discussion (Misc queries) | |||
Data row wise, formula column wise | Excel Discussion (Misc queries) | |||
Delete row wise duplicates & colomun wise simultaneously excel | Excel Worksheet Functions |