Return date from another cell's text

Question

Is there a way to have a cell return a future date based on a specific word being present in the adjacent cell?

I have a sheet with information regarding my work projects and have entered a user defined function to return either 'TRUE' or 'FALSE' in column M based on the previous L column cell's colour (so if the cell colour is green the adjacent cell returns the text 'TRUE' and if not green it returns 'FALSE'). Next, I want to enter a date in column N based on column M's contents; if 'FALSE, the adjacent cell should return the date it will be 1 week from today (today being whatever the date is that I'm working on the document), if 'TRUE' it should remain blank.

Here is the code for the user defined function. I found this on AbleBits and adjusted based on the specific colours I wanted:

Function IdentifyColor(CellToTest As Range)

---

'Returns (198 * R) + (239 * G) + (206 * B)

'IdentifyColor = 13561798 for green, etc.

IdentifyColor = CellToTest.Interior.Color

---

End Function

I have tried using the IF function with the TODAY function and typing '+7' but my cell keeps returning blank even though it is 'FALSE'. I entered it like this:

=IF(M2="FALSE", TODAY()+7, "")

I wondered whether it's because the cells I'm trying to use in the formula are themselves results of a formula, should this make a difference?

I also wondered whether it's because I don't have a cell containing the current date anywhere in the sheet, although I would think the TODAY function would know what date it is already so shouldn't need to reference a cell for that information. But I am no expert on Excel so any advice anyone could offer would be greatly appreciated.

Hopefully someone will be able to help as I can't find anything online about this currently.

Thanks in advance.

Amy

Answer 1

Instead of "FALSE", use FALSE:

=IF(M2=FALSE, TODAY()+7, "")

Answer 2

Ah perfect, thanks so much!

As I said, I'm no expert but I didn't even think it might be the quotation marks that were causing the issue.