if date statement or formula

N

novice33

I am trying to create an If statement that would populate a 1 in another cell
by reading the date in the cell next to it. For example, in cell A2 I have
12/12/2005, I want cell B2 to populate with a 1 if there is a date. More or
less, every time a date is entered in Column A, I want Column B to have a 1
next to it. I would appreciate any help given.
 
D

Dave Peterson

Dates are just numbers to excel.

Can you just check to see if that cell in column A is numeric?

=if(isnumber(a1),1,0)
or
=--isnumber(a1)

(The -- converts true/false to 1/0's.)

If you have other numbers in that column, maybe:

=IF(AND(ISNUMBER(a1),LEFT(CELL("format",a1),1)="D"),1,0)
or
=--AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="D")
or even
=ISNUMBER(A1)*(LEFT(CELL("format",A1),1)="D")
(multiplying is enough to do that conversion)
 
G

Gary''s Student

First enter this tiny UDF:

Function vba_isdate(r As Range) As Boolean
vba_isdate = IsDate(r)
End Function

Then in B1 enter:

=IF(vba_isdate(A1),1,"") and copy down
 
D

Dave O

Following your example, the formula in B2 might be
=IF(A2<>"",1,"")
This evaluates A2 and returns a 1 if the cell contains anything, and
returns a blank if A2 is blank.
 
C

CLR

If there will be nothing else in column A except either dates or a blank
cell, then this in B1 and copied down will do..

=IF(A1>0,1,"")

If column A could contain either TEXT or a date or blank, then this

=IF(AND(ISNUMBER(A1), A1>0),1,"")

If column A could also contain numbers, then only specific date ranges
"might" be identified....as just the number 1 can actually be formatted and
considered a "date"...



Vaya con Dios,
Chuck, CABGx3
 
Top