# Find next scheduled date in range

#### kripper

##### Board Regular
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.

MR.EXCEL RULES

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### kripper

##### Board Regular
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.

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-known Member
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

##### Board Regular
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......

Last edited:

#### DanteAmor

##### Well-known Member
Thanks again my friend.......that works amazingly as well.

I love this forum......

I love the forum too.

Replies
2
Views
64
Replies
27
Views
1K
Replies
1
Views
358
Replies
3
Views
2K
Replies
3
Views
257

1,127,758
Messages
5,626,691
Members
416,200
Latest member
Pulsar3000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back