Extracting time data

[Dandada26]

I’m doing a time control sheet. I have the following data sheet:

Book1
	B	C	D	E	F	G	H	I	J
1	AgentID	LoginTime	LogoutTime	LogoutDate	Correlation ID+Date	ActualWorkedDay	TotalWorkedHours
2	17581	18:12:00	18:18:00	11/16/05	1758138672	11/16/05	0.10		3:00:00
3	17581	19:00:00	1:00:00	11/16/05	1758138671	11/15/05	6.00
4	17581	9:59:00	11:28:00	11/17/05	1758138673	11/17/05	1.48
5	17581	13:15:00	22:00:00	11/17/05	1758138673	11/17/05	8.75
6	18423	13:05:00	17:18:00	11/16/05	1842338672	11/16/05	4.22
7	18423	17:59:00	0:00:00	11/16/05	1842338671	11/15/05	6.02
8
9
10
11		AgentID	17581
12
13		11/16/2005			11/17/2005
14		Wednesday			Thursday
15	Log	In	Out		In	Out
16	Actual	18:12:00	18:18:00		9:59:00	22:00:00
Sheet1

From this data I want to extract data for a single agent by day of the week. I wanted an alternative to the array formula, which has the following limitations:
1- The array formula I used doesn’t work beyond the 1500 rows, my row extended around the 10.000.
2- Since some agents shift end on the next day. The formula can’t identify the last logout for the shift.

 

[RalphA]

1. The formula in D2 should be, to show the date:
=IF(B2="","",B2&CHAR(1)&TEXT(G2,"mm/dd/yyyy"))

2. I don't see any "array" formula. Could you explain?

3. What do you have in J2?

 

[Dandada26]

The array formula is in cell C16 and D16

 

[Dandada26]

The time in J2 is for excel to consider the shift that ends after midnight belong to the previous day. It makes a cut at 3:00 am

 

[barry houdini]

So are the results shown in C16, D16, F16 and G16 what you want?

If not, what should they be

 

[Dandada26]

The answer is the one I want but if the amount of data pass the 1500 the array formula stops working due to memory limitations I suppose. So I need a formula that will do the same beyond the 1500.

 

[Dandada26]

The 1500 I'm referring to are rows. Does this have a solution or I'm asking for too much?