Need to determine date prior

[Buttertoast]

Hello!

New here. I need some expert guidance about a spreadsheet I am working on and will be working on in the future. I have attached a copy of the spreadsheet. Basically, what I need is to determine an earlier date when I can accommodate delivery. There are several conditions and I hope anybody can help me design this spreadsheet more efficiently.

Column L must determine a date an earlier date where a delivery could be possible. The conditions are: This date must be on a day indicated on column L; Must belong on it's designated week based on column J. (E.G. Week 1 would be Feb. 3-7, Week 3 would be Feb 17-21..) And that the date must be earlier prior reset date (Column E).

If you can see on Row 4: M4 is the Friday where this customer could be delivered earlier than reset date (2/17/2020)

Hope anyone can help.

 

[Toadstool]

Hi Buttertoast!

I have a few questions and observations:

1. You say "must be on a day indicated on column L" but it would be better if you said you only work Mondays to Fridays if that's the case. What about Holidays such as July 4th?
2. How do you calculate the week number in a month? I suspect it may be something like
   =WEEKNUM(date of interest,16)-WEEKNUM(DATE(YEAR(date of interest),MONTH(date of interest),1),16)+1
   This would give you some months with 5 weeks (and a couple with 6 weeks but those are always short weeks over a Saturday and/or Sunday).
3. It is tricky extracting numbers from text so instead of the Week being "1 & 3" it is probably easier to have 5 columns headed 1 2 3 4 5 and put in the numbers or an "x" in the appropriate column.
4. What is the earliest date which can be selected? I assume there's some lead time to arrange a changed delivery so is it today plus 1 day, today plus 2 days?
5. The easiest solution may be to have a Calendar tab with columns of every date in the year and their week number. Then you could do a lookup for a date greater than the earliest date but less than the Reset Date and with a matching week number.

I hope that helps.