Excel VBA Course
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.)

View Course

Add a condition to an existing formula


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.



Selected Answer

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


"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.


 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.



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

Answer the Question

You must create an account to use the forum. Create an Account or Login