How to get the filter rows to always get the first and last row in a daterange

Is the date and time in the same cell?

Here's one way to do it...

Data Range

	A	B	C	D	E	F
1	Date	In/Out	------	Date	In	Out
2	2-May-14	9:00		2-May-14	9:00	19:14
3	2-May-14	19:14		5-May-14	9:31	19:38
4	5-May-14	9:31		6-May-14	9:31	20:11
5	5-May-14	19:38		7-May-14	8:36	12:45
6	6-May-14	9:31		8-May-14	8:45	19:54
7	6-May-14	12:56
8	6-May-14	13:10
9	6-May-14	20:11
10	7-May-14	8:36
11	7-May-14	12:45
12	8-May-14	8:45
13	8-May-14	12:35
14	8-May-14	13:23
15	8-May-14	19:54

You can use advanced filter to extract the unique dates and copy them to another location (like column D in the example).

Then, you can use these array formulas** to get the In and Out times for each date.

This array formula** entered in E2:

=MIN(IF(A$2:A$15=D2,B$2:B$15))

This array formula** entered in F2:

=MAX(IF(A$2:A$15=D2,B$2:B$15))

Format E2:F2 as time h:mm then copy down as needed.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Looks like you have a lot of data.

If you're happy with your current solution then by all means continue to use it.

Another possibility would be to use a pivot table. Unfortunately, I never use pivot tables so I can't tell you how to set it up.

Let me see if I can get the attention of someone that is good with pivot tables.

Or with an pivot table.

See the attached file.

Thanks for the reply.

Glad I could help.

You're welcome!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.