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