update column in excel

The previous formula checks if cell B1 has any value. If you need to check if it contains a valid date, the use this formula instead:

=IF(AND(ISNUMBER(B1),B1>NOW()-3650,B1<=NOW()+3650),"Yes","")

jppinto

Please check attached example...
ISDATE-Formula.xlsx

Hi zachvaldez,

Note that jppinto's last solution returns "Yes" only if the date is within 10 years of the current date.  If that's not adequate for your needs, you might have to adjust the "3650" numbers (which are the number of days from the current date).

I was thinking something like this:
    =IF(ISERR(DATEVALUE(B1)),"","Yes")
but I can't quite get it to work, because DATEVALUE(B1) is always returning a "#VALUE!" error, for some reason.  Maybe DATEVALUE() can't reference a difference cell?

I expect JP's first solution is sufficient but if you need to check for a date explicitly.......

...DATEVALUE function doesn't work on true dates, it returns a date value when it references a text-formatted date. One way to check is like this:

=IF(OR(CELL("format",B1)={"D1","D2","D3","D4","D5"}),"Yes","")

That will return "Yes" for dates in most formats

regards, barry

barry - you need to have a couple pints, then sit down at the keyboard and write a couple articles.  This one is a GEM and I have seen you use it a couple times but keep forgetting it.

Dave

Hi Barry,

Your formula:
    =IF(OR(CELL("format",B1)={"D1","D2","D3","D4","D5"}),"Yes","")
just works on the "format" of the cell, rather than the "contents", though, right?  I'm not sure whether that will help zachvaldez, because his entire B column may be the same format (e.g. a date format), and the above formula can return "Yes" even if B1 is empty.

Thanks for the info about DATEVALUE.  I tried putting things like:
    '1/2/2012            (with the leading quote)
in B1, and it worked, but I doubt that will help zachvaldez.

@tel2 - almost there.  What about:

=IF(ISERROR(DATEVALUE(TEXT(G8,"mm/dd/yyyy"))),"","Yes")    ??

Although there's not TRUE way to validate whether a random cell is a date - could be a formatting problem, could be a numeric that just happens to evaluate to be > = 1/1/1900 and <= 12/31/9999. But if both are true, got better odds, anyway.

Of course, the Asker may know darn tutin if there's something in that cell, then its a date and jppinto's formula is A-Ok.

Cheers,

Dave

The first solution is enough in my case. I appreciate the other contributions though which I find extremely useful in other situations.

very useful