Immediate next future date

[Muthukrishnan V]

Excel 2007.
Columns R5 to R187 contain dates in dd-mm-yyyy format.



My requirement: Column R1 should show the next immediate date following
the system date.


Example: System date in dd-mm-yyyy : 04-05-2019
Column R25 contains 06-05-2019 (next earliest date upcoming)
Column R14 contains 03-05-2019 (this date is past)
Column R94 contains 17-06-2019
Column R80 is blank.

In the above example, I want column R1 to show 06-05-2019 (being the next immediate occurring date)



Request formula.

Thanking you,

 

[Yongle]

Hi

The system date formula is
=TODAY()

For the next day
=TODAY() + 1

format cell R1 as date

 

[Muthukrishnan V]

I am sorry. I do not want the next date (i.e tomorrow) to appear in column R1.
I want, from the dates appearing in R5 to R187, the next occurrence of date. Suppose
today is 04-05-2019 and the earliest future date is 15-06-2019, R1 should display 15-06-2019.
Kindly advise formula

 

[Yongle]

Is this what you want?

=MINIFS(R5:R187,R5:R187,">="&TODAY())

and if it must be after today...
=MINIFS(R5:R187,R5:R187,">"&TODAY())

Edit,,
Just remembered EXCEL2007 probably does not have MINIFS

Will update ltomorrow with a different formula

 

[Yongle]

For Excel 2007 try ...
array FORMULA
=MIN(IF(R5:R187>=TODAY(),R5:R187))
commit with {CTRL}{SHIFT}{ENTER} otherwise expect#VALUE! error

Is the same as
=MINIFS(R5:R187,R5:R187,">="&TODAY())

 

[Muthukrishnan V]

Thank you Sir,

 

[Muthukrishnan V]

Sir, I tried array formula and when committed with Ctrl Shift Enter keys, shows "Value".

 

[Yongle]

If the formula in the cell looks like this - it is wrong
=MIN(IF(R5:R187>=TODAY(),R5:R187))

If the formula in the cell looks like this - it is correct
{=MIN(IF(R5:R187>=TODAY(),R5:R187))}


Double-click on the cell R1 to edit
instead of {ENTER} use {CTRL} & {SHIFT} & {ENTER} together
Like this:
press CTRL
keep pressing on CTRL
press SHIFT
Keep pressing on CTRL & SHIFT
press ENTER

Excel 2016 (Windows) 32 bit

	R​	S​
1​	05/05/2019​	{=MIN(IF(R5:R187>=TODAY(),R5:R187))}
2​
3​
4​
5​	03/05/2019​
6​	08/05/2019​
7​	01/05/2019​
8​	07/05/2019​
9​	04/05/2019​
10​	07/05/2019​
11​	01/05/2019​
12​	08/05/2019​
13​	03/05/2019​
14​	07/05/2019​
15​	02/05/2019​
16​	06/05/2019​
17​	02/05/2019​
18​	03/05/2019​
19​	04/05/2019​
20​	05/05/2019​

Sheet: Sheet1

 

[Muthukrishnan V]

Thank you Sir, It works fine. I get correct result. Thanking you once again.

 

[Yongle]

thanks for your feedback