How do I create a the formula using the following conditions for todays date in cell A2:

0 would be for blank cells or cells that have the word "COMPLETE" or "N/A" in them

1 would be for cells that are 7 prior to today's date

2 would be for cells that are 8 to 14 days prior to today's date

3 would be for cells that are 15 days prior to today's date

4 would be for dates in the future from today's date

I am attaching a sample spreadsheet. Column C shows the value that should show in Column D using the formula.