Need to calculate some complicated dates

[Graesen]

Hello everyone. Thanks for looking.
I have an excel issue that I'm hoping someone can help me with. I've been looking for a solution for a while but nothing I've tried so far works 100%.
I'd love to hear your thoughts and any possible solutions.

Here's my problem.
I'm sorry that I can upload a file to the board but would gladly send a copy of my file to anyone who asks.

I need to calculate some specific dates based on a couple of variables
A number (between 00 and 99) is used to calculate a weekly pattern (A or B, i.e Weeknum = ODD/EVEN) and a Week day (Mon-Fri, where 0-19 = Mon, 20-39 = Tue, etc.)
So far, so good, but this is where the fun starts!

From a specified date (C3) I need to calculate a date equal to the first appropriate weekday after C2, in the appropriate weekly pattern
and then,
From the same date in C3, a date for the first alternative pattern weekday unless that date is in the same week as the date in C3 in which case the date needs to advance to the next week in the appropriate pattern.

Sorry, this is complicated and I probably haven't explained things well. Please feel free to ask questions if you need to!

I've given a couple of examples of what the output vs. input should be below in the hope that you can make sense of the task.
Any potential help would be appreciated. I've tried a few formulas but not one has been 100% reliable.
I've not added any formulas here to avoid confusing you as I think my current solution is ugly (and mainly because it doesn't work!)

Thanks for your help.
Graesen.

A	B	C	D	E	F
1	input 00-99	18		Weekly Pattern	A
2
3	Input Date	02/07/2018		Week Day	Monday
4
5				first ON date
6
7				first OFF date

<tbody>
</tbody>

User inputs a code number in C1 and a date in C4 Weekly pattern is decided by the input in cell C1, where an odd number = pattern A and an even number = pattern B week day is decided by the data in cell C1, 00-19 = Monday, 20-39 = Tuesday, etc. I need to calculate: 1. a (date) value in F6 which is equal to the first weekday in the pattern calculated in F2 2. a (date) value in F8 which is equal to the first weekday in the opposite pattern to F2, unless the result is in the same work week as F2, in wich case the output needs to be in the next appropriate (F2)week. e.g. if the date in C4 = 02/Jul/2018 and C2 = 10. First On date = 16/Jul/2018 and first Off date = 09/Jul/2018 if the date in C4 = 02/Jul/2018 and C2 = 11. First On date = 09/Jul/2018 and first Off date = 23/Jul/2018 if the date in C4 = 24/Jul/2018 and C2 = 88. First On date = 03/Aug/2018 and first Off date = 27/Jul/2018 if the date in C4 = 11/Jul/2018 and C2 = 79. First On date = 12/Jul/2018 and first Off date = 19/Jul/2018

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[TheMathLab]

Hi Graeson

Check out this Google Sheets: https://docs.google.com/spreadsheets/d/18Nyyz1pyOJAXy5ynEO1Mw95PqIVItGI1nB8BOqKGM9k/edit?usp=sharing
It's not quite Excel, but is mostly the same. I checked the calculations in Excel and it works the same.
Note: Dates in Google Sheets are formatted by default as mm/dd/yyyy (which is annoying for us that go dd/mm/yyyy)

I've got the First ON Date to calculate a date but it doesn't take into account the weekly pattern. I couldn't understand your explanation, sorry.

 

[Tetra201]

@ Graesen:

In three of your four examples (1, 3, and 4), the ON and OFF dates differ by a week. In Example 2, they differ by two weeks. Could you please clarify why?