I have a column type Date (the date is not ordered), and Today(), how can I find the closest future date to Today() and highlight a range of cells in the row?
Example: A1=07/20/20, A2=02/19/19, A3=02/19/21 (mm/dd/yy), so when I enter this spreadsheet today, A1 will be highlighted, if I enter this spreadsheet a week from now, A3 will be highlighted.
Thanks
Sample file: future_date.ods
Funny!
I don’t know a locale for which both these examples from the original question would be “valid” dates: A1=20/07/20, A2=02/19/19.
Did I miss something?
What about using an unambiguous format and doing away with the crazy mess we have?
Sorry for the type and thanks for reminding me, it’s fixed.
It’s fixed the very stubborn, internationally deprecated, and ourtdated US way only.
A “+”, however for using examples making clear the dates are “mid-endian”.
Thanks Mike for this tip, I can only make this work (to highlight a single cell) if I have a temporaty result cell (with this formula) outside of the date column, if I put this formula directly into Conditional Formatting, it does not work (it highlighted several columns with date > TODAY(), maybe I’m doing something wrong.
Please describe what exactly do you put into the conditional format. Actually, you should compare the current cell with the found minimal value - so continuing the example (the values are in column A), you need to set this formula to conditional format of range A:A:
=A1=MINIFS(A:A;A:A;">="&TODAY()+1)Thanks Mike, this is what I did Format > Conditional > Manage > Edit (Condition 1 was already created) > Condition 1 > Formula is > =A1=MINIFS(A1:A3;A1:A3;">="&TODAY()+1 (using the above example), when I applied this formula to my spreadsheet, ALL dates bellow the next future date are highlighted.
ps: the = sign before A1 disappeared as soon as I clicked OK (the screen shows only A1=MINIFS(A1:A3;A1:A3;">="&TODAY()+1
I added a sample file in my original post with 4 date columns.
Ah! So you used A1:A3 syntax, which means relative addressing. In the case of the specific formula you show in the comment above, it means literally this:
“highlight the cell if it has the same value as the minimal value of the range of three cells starting from this specific cell”. So for each of cells in your conditional formatting range, own lookup range was ised.
You need to use this formula instead:
A1=MINIFS($A$1:$A$3;$A$1:$A$3;">="&TODAY()+1)
(the trailing closing bracket restored).
Yes it’s normal that leading = is not shown in conditional formatting formula.
Thanks so much for your clarification, it’s working!!