Porblem displaying next 2 dates

[john dalton]

Hi I have a problem trying to display dates from a range that also includes text, I need a formula to display the current soonest and then next soonest dates and display them.

The soonest date must also be displayed if is equal to todays date.

Please see attached sheet.

Many thanks

JD

[Pete_UK]

In your sample file I inserted some blank columns to move your dates over so that they started in column J (only goes to column S, though), then you can use this formula in B5:

=IF(ISNUMBER(MATCH(TODAY(),J5:T5,0)),INDEX(J5:T5,MATCH(TODAY(),J5:T5,0)),INDEX(J5:T5,MATCH(TODAY(),J5:T5)+1))

and this one in C5:

=IF(ISNUMBER(MATCH(TODAY(),J5:T5,0)),INDEX(J5:T5,MATCH(TODAY(),J5:T5,0)+1),INDEX(J5:T5,MATCH(TODAY(),J5:T5)+2))

Then you can copy the two formulae into B11:C11 and into B18:C18 to give you the results you indicate.

Hope this helps.

Pete

[JohnTopley]

For "current Appt"

=MIN(IF($F5:$O5<>"Finished",IF($F5:$O5>=TODAY(),$F5:$O5,"")))

Enter with Ctrl+Shift+Enter

for "Next Appt"

=INDEX($F5:$O5,MATCH($B5,$F5:$O5,0)+1)

[john dalton]

Pete, superb as always, cheers mate .