0

I have a column in Excel that contains a list of dates, say for example

02/09/13

and then in another column I have a formula to detect whether or not the date is 6 months older than today, and if it is, it should display "True" in this column:

02/09/13            True

my formula so far is 

=DATEDIF(E17,TODAY(),"m")>=6

My question is if the column that contains the dates contains certain text, for example:

|   02/09/13         |   True    |

|   No date present  |           |

How would I get the "true/false" column to detect this text and print it instead of true or false?

I have tried numerous different formulas with ORs/ANDs, nested IFs and multiple IFs, etc.

=IF(E17,TODAY(),"m")>=6, IF(E17 = "NO DATE PRESENT", "NO DATE PRESENT")

=DATEDIF(E17,TODAY(),"m")>=6, OR(E17 = "NO DATE PRESENT", "NO DATE PRESENT")
TylerH
  • 20,799
  • 66
  • 75
  • 101
user2012630
  • 33
  • 1
  • 1
  • 6

3 Answers3

3

Maybe this:

=IF(E17="NO DATE PRESENT","NO DATE PRESENT",DATEDIF(E17,TODAY(),"m")>=6)

or for general case:

=IFERROR(DATEDIF(E17,TODAY(),"m")>=6, E17)
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
1

Dates are Numbers. So you can check if it is a number via a function and if not then print it.

In addition some Numbers may be not plausible so you could check the number for its range and if it is implausible also print it.

Johannes
  • 6,490
  • 10
  • 59
  • 108
1

You could test if datedif returned an error (which it would if the cell contained text rather than a date), and take appropriate action from there.

For example:

=IF(ISERROR(DATEDIF(E17,TODAY(), "m")),E17,DATEDIF(E17,TODAY(), "m"))
Chris
  • 8,030
  • 4
  • 37
  • 56