Checkbox to return a date

It could be simple as MAXIFS( B, A, true ), but the function is only available on the latest Excel.

So, my solution will be like this.

1. Add an assistant data column to the source table with formula to put the date if checked.

e.g. C2 = IF(A2,B2,"")

2. Get the maximum value in the

e.g. the_date = MAX(Source::C)

Or, it will become a little complicated if you need some data ( other than 0 ) in case of no data with check on

the_date = IF(COUNT(Source::C)=0,"NA",MAX(Source::C))

HI WIB,

Keeping it uncluttered is a simple matter of hiding the auxiliary column.

If you want to keep an unbroken string of column letters (A, B, C, D, E...) when the table is activated, make the auxiliary column the furthest right on the table.

Hiding the column, and keeping it separated from the data entry columns also helps prevent accidental damage to formulas in that column.

Here's the same solution as presented by To_Mi, with the auxiliary coumn (D) visible on the left, and the same table with that column hidden on the right:

Regards,

Barry

There might be a simpler way, but this is what I'd do:

Create a row somewhere (beside the dates if you like, or elsewhere if you prefer). In a cell out of the way somewhere, enter a date that's guaranteed to be older than anything your spreadsheet will ever see. Then, in the cell that corresponds to the first date and checkbox you have, type:

• =
• if (click checkbox cell, if-condition)
• , (click date cell, if-true/checked)
• , (click ancient date cell, if-false/unchecked)

So if you have the ancient date in E2, a real date in A2 and a checkbox in B2, and want the result in C2, your formula should look like:

=if(B2, A2, $E$2)

Note that those dollar-signs tell Numbers to "preserve" the row and column of E2 when you paste that formula — meaning, as you paste along, that cell's reference will remain the same rather than moving as you paste. Very important, since you put the ancient date in only one cell.

Once you've pasted that formula all along your row or column of dates, go to the cell where you want the latest date to display. Enter:

• =
• max(
• (select entire row or column of your if-formulas that combine the date with the checkbox)
• (insert close bracket)

So if you had dates in A2 through A8, and put the results in C2 through C8, your formula should look like:

=max(C2:C8)

That cell will show you the largest/maximum/latest date which was checked off.

Edit:

And of course, if you have different tables etc, just go click the cells you want in your formulae — don't even try to type table names in addition to cell names! 😝