Working With Different Dates in Numbers

Assuming your data is sorted by date then you can try something like this:

Make sure the dates in the DATE column are true dates, by default right-aligned in Numbers. (Your formats don't work in my region so I had to change them so Numbers recognized them as dates).

In the added Month column in Table 1, this is the formula in B2, filled down.

=MONTHNAME(MONTH(A2))

This simply gets the name of the month for the date in column A.

In the Month column of Table 2 I made sure the month name was text (and not date-time formatted to look like a month name) by typing ' followed by July. I could also have formatted the cells in the column as Text and then entered the month name.

The formula in B2 is:

=INDEX(Table 1::C,MATCH(A2,Table 1::B,-1))

The MATCH finds the location of the first cell in column B of Table 1 that matches the month in column A and INDEX uses that location to look up the value in column C.

The formula in C2 is:

=INDEX(Table 1::D,MATCH(A2,Table 1::B,1))

The MATCH finds the location of the last cell in column B of Table 1 that matches the month in column A and INDEX uses that location to look up the value in column D.

SG

Hi MW,

It suspect it would have been easier to tell you how to do it if you provided a clearer explanation of what it is you were trying to do.

Having said that, think I may have guessed what you are attempting.

Your small table shows July in cell A2.

In cell B2 you want the data from column B of the larger table that is on the same row as the earliest date and time value in July in column A.

In cell C2, you want the data from column C of the larger table that is on the same row as the latest date and time value in July in column A.

If that is correct, the example below should solve the issue. For the example, I have copied only the two data figures you want copied into the smaller table, and filled the unwanted cells with numbers consisting of repeated digits—1s and 2s for the dates that are too early, 3s and 4s for the dates that are too late. I've also added an August date to provide a date that is later than either of the ones you want to retrieve data from in this row.

The shorter formula shown below the tables is in B2 of the smaller table; the longer one is in cell C2. Both formulas may be filled down into additional rows if you want to collect the data for more than a single month.

The value in A2 of the smaller table must be a date and time value, entered as the date of the first day of the month whose data is to be retrieved in B2 and C2. The entry must include the year. If the time part is included, it must be 00:00:00.

Syntax for INDEX is: INDEX(range,row-index,column-index)

range defines the group of cells from which a value is to be retrieved.

in the shorter formula range is Table 1::B (all of column B) and in the longer formula it is Table 1::C (all of column C)

row-index tells INDEX which row contains the cell from which the value is to be retrieved.

In each of these formulas, MATCH provides the row-index value.

As range is a single column in both formulas, column-index is not needed.

Syntax for MATCH: MATCH(search-for,search-where,matching method)

In the shorter formula,

search-for is the value in cell A2 of 'this table' (Midnight, at the beginning of July 1, 2018)

search-where is column A of Table 1

matching method is "find smallest value" meaning 'find the smallest (earliest) date and time that is the same as or larger (later) than the search-for value.

In the longer formula,

search-for is provided by EDATE(A2,1) (syntax: EDATE(start-date,months-offset)

A2 contains the date July 1, 2018. the months-offset value, 1, makes EDATE return February 1, 2018.

-DURATION(,,,1) subtracts one minute from that to make search-for July 31, 2018 11:59.

search-where is the same set of cells—all of column A of Table 1—as in the shorter formula.

match-method is find largest value, meaning 'find the largest (latest) date and time that is smaller (earlier) than or the same as the search-for value.

In B2, MATCH returns 5, the position in the table of the earliest date and time in July, 2018, and Index returns the value from the fifth row of column B.

In C2, MATCH returns 12, the position of the latest date and time value in column A, and INDEX returns the value from the 12th row of column C.

Regards,

Barry