 ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# Add a condition to an existing formula

0

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.

0

The formula below will "almost" do what you want.

``=IF(ISNUMBER(\$B3),MATCH(TODAY()-\$B3,{-1000,0,8,15},1)-1,-1)``

"Almost" because your suggested code isn't "Excel friendly". The results the formula will produce are as follows.

1. Future dates: 0
2. 0 to 7 days back: 1
3. 8 to 14 days back: 2
4. 15 or more days back: 3
5. No date value in column B: -1

Return values 0 to 3 are determined by the MATCH function -1 (that is the first of the two "-1" at the end of the formula). If you remove that "-1" the sequence would be 1 to 4 with the same meaning. However, if you wish to have the result of 4 for future dates (meaning, you change the logical sequence) the formula would become twice as long.

The final "-1" in the formula is the result for non-date values. You can change that to anything, including "" or "come back tomorrow" or a value like 4 or a reference like \$B3.

-1

Please note two corrections: 1)  This post is asking about creating a new formula, not "add[ing] to an existing formula"; and 2) the date from which the formula needs to be created is in column B (B2) and not A2.

### Discussion

You can always edit your original question.
Variatus (rep: 4889) Jan 11, '18 at 10:54 pm