Find first and last event each day

[BravoBravoAu]

Hi guys,

I have a workbook with thousands of rows of data for a two year period. I'm looking for a way to find the first and last event for each day - some days have hundreds of events.

As it is, Column C contains the date and time (format: custom = dd/mm/yyyy h:mm) with other columns containing other information relevant to the event. Does anyone have an idea how to filter or find the first and last event of each day? For instance below. Happy to reformat if needed, including splitting time and date across two columns.

Date 28/04/2017 12:15 28/04/2017 8:00 28/04/2017 7:25 28/04/2017 6:27 27/04/2017 15:59 27/04/2017 12:20 27/04/2017 12:08 27/04/2017 12:08 27/04/2017 11:02 27/04/2017 10:29 27/04/2017 6:36 26/04/2017 16:02 26/04/2017 14:27 26/04/2017 14:14 26/04/2017 14:07 26/04/2017 12:34 26/04/2017 12:25 26/04/2017 12:16 26/04/2017 10:32 26/04/2017 10:29 26/04/2017 10:24 26/04/2017 10:20 26/04/2017 7:57 26/04/2017 7:21 26/04/2017 6:48 Thanks in advance. <tbody> </tbody>

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

 

[alansidman]

Use the Max function and the Min functions.

Look here: https://www.techonthenet.com/excel/formulas/max.php

https://www.techonthenet.com/excel/formulas/min.php

 

[BravoBravoAu]

Thanks Alan, any idea how I might apply those functions to the issue?

 

[alansidman]

Since Dates and time in Excel are numbers based on January 1, 1900. Days are the integer portion of the number and time is the decimal portion. These numbers are represented as dates and time through formatting.

So in a cell to find the earliest =Min(range of date, ie A1:A30). The same syntax for =Max since you are looking at numbers formatted as dates and time.

To understand this better, look at this link. http://www.cpearson.com/excel/datetime.htm

 

[BravoBravoAu]

Again Alan, thank you.

That seems to be to cater to the entire sheet rather than finding the first and last even for EACH day (over a two year period).

 

[alansidman]

ok. We are close. Look at this link on Maxif and MinIf. That will solve your issue. Because you are using European Date Format, I didn't check that there were more than one days listing. It is not a familiar setting for me and I kind of glossed over that issue.

http://www.contextures.com/excelminmaxfunction.html

 

[Aladin Akyurek]

Let A1:A26 house the data, including the header.

Date		4
4/29/2017 12:45		date	first	last
4/29/2017 0:00		42851	4/26/2017 20:24	4/26/2017 23:51
4/28/2017 22:15		42852	4/27/2017 7:00	4/27/2017 19:48
4/28/2017 19:21		42853	4/28/2017 0:06	4/28/2017 23:57
4/28/2017 23:57		42854	4/29/2017 0:00	4/29/2017 12:45
4/28/2017 13:00
4/28/2017 12:24
4/28/2017 12:24
4/28/2017 9:06
4/28/2017 7:27
4/27/2017 19:48

<tbody>
</tbody>

In C1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(A2:A26),INT(A2:A26)),INT(A2:A26)),1))

In C3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$2:C2)>$C$1,"",MIN(IF(ISNUMBER($A$2:$A$26),IF(ISNUMBER(MATCH(INT($A$2:$A$26),$C$2:C2,0)),"",INT($A$2:$A$26)))))

In D3 control+shift+enter, not just enter, and copy down:

=MIN(IF(ISNUMBER($A$2:$A$26),IF(INT($A$2:$A$26)=$C3,$A$2:$A$26)))

In E3 control+shift+enter, not just enter, and copy down:

=MAX(IF(ISNUMBER($A$2:$A$26),IF(INT($A$2:$A$26)=$C3,$A$2:$A$26)))

 

[BravoBravoAu]

Thank you Aladin Akyurek - that solution worked a treat!!

 

[Aladin Akyurek]

Thank you Aladin Akyurek - that solution worked a treat!!

You are welcome. Thanks for the update.

 

[whitehorse091]

Let A1:A26 house the data, including the header.

Date		4
4/29/2017 12:45		date	first	last
4/29/2017 0:00		42851	4/26/2017 20:24	4/26/2017 23:51
4/28/2017 22:15		42852	4/27/2017 7:00	4/27/2017 19:48
4/28/2017 19:21		42853	4/28/2017 0:06	4/28/2017 23:57
4/28/2017 23:57		42854	4/29/2017 0:00	4/29/2017 12:45
4/28/2017 13:00
4/28/2017 12:24
4/28/2017 12:24
4/28/2017 9:06
4/28/2017 7:27
4/27/2017 19:48

<tbody>
</tbody>

In C1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(A2:A26),INT(A2:A26)),INT(A2:A26)),1))

In C3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$2:C2)>$C$1,"",MIN(IF(ISNUMBER($A$2:$A$26),IF(ISNUMBER(MATCH(INT($A$2:$A$26),$C$2:C2,0)),"",INT($A$2:$A$26)))))

In D3 control+shift+enter, not just enter, and copy down:

=MIN(IF(ISNUMBER($A$2:$A$26),IF(INT($A$2:$A$26)=$C3,$A$2:$A$26)))

In E3 control+shift+enter, not just enter, and copy down:

=MAX(IF(ISNUMBER($A$2:$A$26),IF(INT($A$2:$A$26)=$C3,$A$2:$A$26)))

Mr Akyurek, I've used your formula well. Thank you for your help. Going one step further, would it be possible to find first and last entries for multiple units in one spreadsheet? Let's say the data in A2:A26 belongs to unit A, and there are data recorded for units B and C within the same period, how do I get the first and last entries for each respective unit?