Excel Formula to Return Value Based on Date and Text Criteria

Formula

To achieve the described functionality, utilize the following formula. Assuming the data starts from row 2, the formula will be:

=IF(AND(L2 >= TODAY() - 7, LEFT(H2, 3) <> "doe"), H2, "")

Explanation of the Formula

1. IF Function: The formula utilizes the IF function, which evaluates a condition and returns one value if true and another if false.
2. AND Function: Combines multiple logical conditions.
   • L2 >= TODAY() - 7: Checks if the date in column L is within the last 7 days.
   • LEFT(H2, 3) <> "doe": Checks if the text in column H does not start with "doe". The LEFT function extracts the first three characters.
3. Return Values:
   • If both conditions are met, it returns the value in column H (H2).
   • If either condition fails, it returns an empty string ("").

Practical Example

Data Setup

Consider the following sample data:

Formula Application

1. Place the formula in cell M2 (assuming it's the next available column).
2. Drag down the formula through the rows where data exists.

Resulting Calculation

For the above example, if today's date is 10/08/2023:

• For Apple (10/01/2023): Output would be "" (not within last 7 days).
• For Doe (10/05/2023): Output would be "" (starts with "doe").
• For Banana (10/10/2023): Output would be "" (not within last 7 days).
• For Cherry (10/07/2023): Output would be Cherry (within last 7 days and does not start with "doe").

The final output in column M will be:

Conclusion

This formula effectively filters the values based on the specified criteria, demonstrating a practical approach to conditional data extraction in Excel. For further advanced Excel skills, consider exploring courses on the Enterprise DNA Platform.