Ask Your Question
0

calc next future date

asked 2020-07-16 14:46:22 +0200

michaelbr gravatar image

updated 2020-07-19 14:17:39 +0200

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: C:\fakepath\future_date.ods

edit retag flag offensive close merge delete

Comments

2

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?
https://xkcd.com/1179/

Lupp gravatar imageLupp ( 2020-07-16 17:27:02 +0200 )edit

Sorry for the type and thanks for reminding me, it's fixed.

michaelbr gravatar imagemichaelbr ( 2020-07-17 11:06:16 +0200 )edit

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

Lupp gravatar imageLupp ( 2020-07-17 11:19:31 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2020-07-16 15:30:15 +0200

updated 2020-07-16 15:31:28 +0200

Consider MINIFS:

=MINIFS(A:A;A:A;">="&TODAY()+1)
edit flag offensive delete link more

Comments

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.

michaelbr gravatar imagemichaelbr ( 2020-07-18 14:45:52 +0200 )edit

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)
Mike Kaganski gravatar imageMike Kaganski ( 2020-07-18 14:50:44 +0200 )edit

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.

michaelbr gravatar imagemichaelbr ( 2020-07-19 14:03:02 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-19 16:53:24 +0200 )edit

Thanks so much for your clarification, it's working!!

michaelbr gravatar imagemichaelbr ( 2020-07-20 14:38:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-16 14:46:22 +0200

Seen: 42 times

Last updated: Jul 19