Using Date ranges to update Status Reports

Hello!!!! I could use some help on solving this issue.

Overview: Attached is an example excel file. The objective of this file is to fill the "Period" column value with the correct and respective status report. We have an assortment of jobs that have scheduled "Start" and "Finish" dates. For this particular data set, I want to update the "Period" column to represent if the job is supposed to: (1) Start Next 2 Weeks (2) Start Next 2-4 Weeks (3) Finish Next 2 Weeks (4) Finish 2-4 Weeks.

Challenge: To easily explain everything, I entered a "Period Date" which is the date that will be used to determine if the Job start or finish dates fall within the next 2 or 2-4 weeks. The "Period Date" can change depending on when you want to update the report. Without having the "Period Date" filled in "A2", is there a way to write VBA code or a formula to let the user select the "Period Date" and have the "Period" column update based on the selected "Period Date"?

Clarification: Assuming there is no column or area to manually input the period date (such as "Period Date") and you only have the table attached. Can you and how do you give the user a way to select the Period Date and based on the selection, update the "Period Column" respectively??

Any help if appreciated. Please let me know if I can provide additional information. Thank you!

Thank you for the replies!

Definitely helpful information.

Is there a way to set the Period Date to the upcoming Friday? Ex., If =Today() returns today's date. Is there a manipulation of this formula that could return the next upcoming Friday?

Thanks!!

FlameRetired,

Thank you for your information! I am nearly all the way to the solution I need thanks to Modytrane and yourself.

The formula to get the next Friday works perfectly. However, the only issue I am running into is: When the date is a Friday, I need that Friday date to stay. Ex., if I use the formula given above =WORKDAY.INTL(TODAY(),1,'1111011') and today's date is Friday, 2/21/2020. I need the formula to return 2/21/2020. However, the formula recognizes it is Friday's date and returns the next Friday 2/28/2020. I only need the next Friday's date to return when Saturday starts.

Is there a solution for this? Thank you again for the help!

Thank you! This worked perfectly. The only thing that needed to be manipulated was the ' ' to " " for the workdays.

Thank you FlameRetired. With everyone's help I was able to get the result I needed.

I wanted to say thank you to everyone for helping with my problem. I do have one more thing I could use help on that is similar to this problem.

Using the excel document attached in the original problem. Assume, the "Job Start" and "Job Finish" columns are filtered to show dates that fall within the next 2/2-4 weeks of the selected "Period Date". So in the document, the 5 jobs are 5 of many but met the criteria of the filters.

The goal: Is there a way to use any of the stuff from above to automate the process of filtering the "Job Start" and Job Finish" columns. Currently, we have to go in and manually select the dates within 4 weeks of the determined "Period Date" to get the jobs meeting the criteria. Is there a better way to do this? Thank you again!!