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

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.

excelformulas-functionsdate-time
posted by: Hedy (rep: 5) on Thu Jan 11, 2018 at 2:09 pm
Answer
Discuss

Answers

0
Selected Answer

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.

posted by: Variatus (rep: 4889) on Thu Jan 11, 2018 at 11:02 pm
Discuss
-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.

posted by: Hedy (rep: 5) on Thu Jan 11, 2018 at 2:29 pm
Discuss

Discussion

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