MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How Do I Pick the Earliest and Latest Time In a Range Of Dates In Numbers?

I have a Numbers spreadsheet in which I enter items based on Date and Time. So the list might look like this(although in reality I usually have about 10 items per day):

Fri, 25 Jan 2019 09:00
Fri, 25 Jan 2019 10:00
Fri, 25 Jan 2019 14:00
Sat, 26 Jan 2019 11:00
Sat, 26 Jan 2019 15:00
Sat, 26 Jan 2019 16:00

As a basic request, I want to pick the earliest and latest time on the latest date. I can pick the latest time on the latest date by using the MAX function or the earliest time on the earliest date by using the MIN function but otherwise I am struggling.

As a bonus request, is there a way of picking the earliest and latest times for each and every day?

—–
Jon E

### Comments: 4 Responses to “How Do I Pick the Earliest and Latest Time In a Range Of Dates In Numbers?”

3 months ago

You might be able to use MATCH for this. Here’s what I tried, assuming that the A column contains the date+times.

In the B column, I use the formula (in row 2):
CONCATENATE(YEAR(\$A2),”-“,MONTH(\$A2),”-“,DAY(\$A2))

This takes the date of 1/25 9:00 in column A and gives me 2019-1-25 in column B. So I have a string representing the date (not the time). I copied that into all rows for B.

Then in C, I used this formula, copied to all rows.
MATCH(B2,B,1)
This gives me the number of the last row with a date that matches this one.
In D, I used:
MATCH(B2,B,−1)
This gives me the first row that matches each date.
Then in row E, I used:
IF(ROW()=C2,\$A2,””)
This puts the date into D only if the current row is also the row indicated in column C. So you can label row E as “last time each day” or whatever.
In F, you have:
IF(ROW()=D2,\$A2,””)
And that row would now be “first time each day.”

For rows E and F you could always use “Last” and “First” instead of the value in column A.

You can always hide rows B, C, and D after this is all set up.

Does that help?

Jon E
3 months ago

Yes that worked perfectly. Thank you so much!
One small caveat. This seems to work providing each item is entered strictly chronologically e.g. the last item of a particular day also happens to be the latest time. But if for example I have this sequence:

Sat, 5 Jan 2019 00:00
Sat, 5 Jan 2019 00:30
Sat, 5 Jan 2019 00:15

The formula will give the result 00:15 instead of 00:30

Is there a formula to return the latest time even if it is not the actual last item of the day?

3 months ago

Jon: It gets more complex if the items are not sorted. But it can be done. Adding to the above, I created cells in Column H like:
MIN(INDIRECT(CONCATENATE(“A”,D2,”:”,”A”,C2)))
This takes the first and last rows of the date, and makes a range like A2:A4. INDIRECT then converts that to an actual range. MIN gets the smallest value.
Then row I is this, to get the MAX value:
MAX(INDIRECT(CONCATENATE(“A”,D2,”:”,”A”,C2)))
This puts the min and max time for each date in every single row.
You could use similar logic to the E and F rows to only show the values in for H and I in rows J and K if the row starts a new date.

Jon E
3 months ago

Again that worked perfectly! I created an extra two columns to only show the single times per day as you suggested and I’m now a happy bunny. Thank you so much again.