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?”

    11 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
    11 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?

    11 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
    11 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.

Comments Closed.