Immediate next future date

Muthukrishnan V · May 4, 2019

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 · May 4, 2019

Hi

The system date formula is
=TODAY()

For the next day
=TODAY() + 1

format cell R1 as date

Muthukrishnan V · May 4, 2019

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 · May 4, 2019

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 · May 4, 2019

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 · May 5, 2019

Thank you Sir,

Muthukrishnan V · May 5, 2019

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

Yongle · May 5, 2019

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

R

[/th][th]

S

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]

05/05/2019

[/td][td] {=MIN(IF(R5:R187>=TODAY(),R5:R187))}[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

03/05/2019