Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!



Excel formula for Next Valid Date in a Daterange matching selected weekdays

By cnwoods ·
I have a spreadsheet in which I have a variable date (theDate), rows for allocated tasks and columns selecting which days of the week each task should happen (x against the day/s it applies, columns F:L starting on Mon), along with start and end dates (cols M&N) for each of the tasks. It looks like this:

Description ... M T W T F S S StartDate EndDate NextDate
Task 1 .......... x ... x ... x ....... 10/2/09 .. 21/09/09 .... ????

I'm probably sleep deprived and overlooking something simple here, but how do I write a formula that checks if theDate falls within the specified daterange, looks up what days of the week it should happen and returns the current date if it's valid, or if not then the next valid date after 'theDate' that it will apply? Note this variable will usually be in the future, but not always (it may be used to recall past tasks).

I'd rather not use VBA if I can get away with it, but if I have to, I have to.

Where I'm at so far:
If theDate < StartDate then StartDate
If theDate > EndDate then ""
If OFFSET(F4(WEEKDAY(theDate,2)-1,0)="x" then theDate
Now I need something that will return how many blank rows it finds to the right of that cell before it hits an 'x', (and come back to F4 (Monday) after it hits L4 (Sunday), then add that number to theDate to return the next valid date, up to a maximum of EndDate.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums