Find next scheduled date in range

[kripper]

Hello again everyone...back to be a pain......

I am trying to figure out a formula to get the next scheduled date from a row. I have my entire months schedule in an excel sheet, with "Rest Days" and "Vacation Days" mixed in with start times.

https://1drv.ms/x/s!Amk_7FQHP36kg-QJndYbzMT0UBfUaw

What I am trying to do is find the next available scheduled date from the dates in row 5 after today, skipping the Rest Days, Vacation Days etc., basically anything that does not have a start time in row 2.

Hoping that makes sense...currently I am using the following formula to find the next scheduled date, and it works to give me tomorrow, but will not work to skip to the next actual scheduled date.

=IFERROR(SMALL(IF($C$9:$NJ$9>TODAY(),$C$9:$NJ$9),1),MAX($C$9:$NJ$9))

Hope someone can tell me where I am going wrong.

Thanks in advance....

MR.EXCEL RULES

 

[kripper]

Hello again everyone...back to be a pain......

I am trying to figure out a formula to get the next scheduled date from a row. I have my entire months schedule in an excel sheet, with "Rest Days" and "Vacation Days" mixed in with start times.

https://1drv.ms/x/s!Amk_7FQHP36kg-QJndYbzMT0UBfUaw

What I am trying to do is find the next available scheduled date from the dates in row 5 after today, skipping the Rest Days, Vacation Days etc., basically anything that does not have a start time in row 2.

Hoping that makes sense...currently I am using the following formula to find the next scheduled date, and it works to give me tomorrow, but will not work to skip to the next actual scheduled date.

=IFERROR(SMALL(IF($C$9:$NJ$9>TODAY(),$C$9:$NJ$9),1),MAX($C$9:$NJ$9))

Hope someone can tell me where I am going wrong.

Thanks in advance....

MR.EXCEL RULES

Well I think I answered my own question after a few more trial and errors....

{=INDEX($C$9:$NJ$9,MATCH(1,(ISNUMBER($C$6:$NJ$6)*($C$9:$NJ$9>TODAY())),0))}

AND IT WORKS.........................

 

[DanteAmor]

Well I think I answered my own question after a few more trial and errors....

{=INDEX($C$9:$NJ$9,MATCH(1,(ISNUMBER($C$6:$NJ$6)*($C$9:$NJ$9>TODAY())),0))}

AND IT WORKS.........................

It can also be:

{=IFERROR(SMALL(IF($C$5:$NJ$5>TODAY(),IF(ISNUMBER(C2:V2),$C$5:$NJ$5)),1),MAX($C$5:$NJ$5))}

 

[kripper]

It can also be:

{=IFERROR(SMALL(IF($C$5:$NJ$5>TODAY(),IF(ISNUMBER(C2:V2),$C$5:$NJ$5)),1),MAX($C$5:$NJ$5))}

Thanks again my friend.......that works amazingly as well.

I love this forum......

 

[DanteAmor]

Thanks again my friend.......that works amazingly as well.

I love this forum......

I'm glad to help you. Thanks for the feedback.

I love the forum too.