Every first time of the day

[mrwul62]

Have been puzzling about the below quite a while. Hope someone can help me out on this.

Column A contains days, multiple rows per day
Column B contains hours, multiple rows per day

I'd like to select/filter the 'earliest' hour (first entry) of each day.

How to filter those?
Example below.


-day time

08-12-2016	09:21:06
08-12-2016	09:20:49
08-12-2016	05:10:06
07-12-2016	16:47:42
07-12-2016	16:44:40
07-12-2016	16:44:25
07-12-2016	08:15:55
07-12-2016	08:15:55
07-12-2016	08:15:55
07-12-2016	08:15:55
07-12-2016	05:08:23
06-12-2016	16:46:59
06-12-2016	16:41:04
06-12-2016	16:40:48
06-12-2016	04:20:05
05-12-2016	17:14:36

<colgroup><col><col></colgroup><tbody>
</tbody>

-etc-

Thanks.
=

 

[mrwul62]

I Think you can use this formula.. It going to work

=IFERROR(IF(COUNTIF($A$2:$A$17,$D2),MIN(IF($A$2:$A$17=$D2,$B$2:$B$17)),""),"")

Thanks,
Dinesh Babu.

problem is that ctrl-shift-enter seems not to work.

this is how it looks like after selecting E2 and then ctrl-shift-enter
(after clipboard copying the formula of course..)


I guess that even if the ranges are not exact, then at least -something- gets in there?
Adjusting the ranges isn't the issue.
=

 

[Legacy 396030]

Put this in E2:

=IFERROR(IF(COUNTIF($A$2:$A$17,$D2),MIN(IF($A$2:$A$17=$D2,$B$2:$B$17)),""),"")


While your cursor is at the end of the formula press the keys CTRL+SHIFT+ENTER at the same time. If you have done this correctly then the curly brackets are at the beginning and end of the formula.

You must enter a date in column D

 

[mrwul62]

I feel so dumb, really.
Have tried and tried and tried...


some examples below
when I do ctrl-shift-enter there I get an error.

(you need to click on the thumbnail and then the magnifier within imgur)
=

 

[mrwul62]

Got it working... (finally)

figured out that with me all comma's should be replaced with semicolons (different language)

so:

=IFERROR(IF(COUNTIF($A$2:$A$887;$D2);MIN(IF($A$2:$A$887=$D2;$B$2:$B$887));"");"")

Also I had to copy all the unique values of the dates into column D


Anyway, it looks like this now.

Many thanks to you all!!




=

 

[Legacy 396030]

See this example:

Excel Workbook
	A	B	C	D	E
1	Date	Time	*	Enter Date	Lowest Time For Date From Column D
2	22-03-2017	13:09:49	*	22-03-2017	12:32:32
3	22-03-2017	12:34:51	*	*	*
4	22-03-2017	12:32:32	*	*	*
Sheet1

 

[mrwul62]

Thanks again.
However, eh ... no offense meant, really not!
But what is the difference?
under 'enter date' I paste a column with unique values of all the dates, the source column is close to 900 rows, I remove all duplicates and paste the result in colunm D.

Anyway, matter is solved now.

=

 

[Scott R]

You could add a formula in Col C which flags your red values.
C1: =IF(B1>=B2,0,1) and copy down

 

[mrwul62]

Ah! Nice indeed!
Thank you!
as I need the earliest time, I guess the formula should be just the other way round.
tried to paste the formula here, but it doesn't work, only first few characters are displayed.
anyway, you will understand.
Thanks for the very good suggestion - without any disrespect to others!

filter on '1' copy / paste results as value in another sheet.

Thanks again!

Date	Time	Filter
22-03-2017	13:09:49	0
22-03-2017	12:34:51	0
22-03-2017	12:32:32	0
22-03-2017	12:32:14	0
22-03-2017	04:33:14	0
22-03-2017	04:32:55	0
22-03-2017	03:09:53	1
21-03-2017	16:41:25	0
21-03-2017	16:39:16	0
21-03-2017	16:38:57	0
21-03-2017	11:20:55	0
21-03-2017	11:18:05	0
21-03-2017	11:07:27	0
21-03-2017	10:30:03	0
21-03-2017	10:22:53	0
21-03-2017	10:22:32	0
21-03-2017	03:29:50	1
20-03-2017	17:09:57	0
20-03-2017	16:59:31	0
20-03-2017	16:59:12	0
20-03-2017	14:14:32	0
20-03-2017	12:57:42	0
20-03-2017	05:39:18	1
19-03-2017	18:59:33	0
19-03-2017	18:57:45	0
19-03-2017	18:57:25	0
19-03-2017	03:57:07	1

<tbody>
</tbody>