If its a date, then...

[Darin Kramer]

HI Guys,

I need a formulae that says if cell a1 is a date, then put a Y, if not,
then put what is currently in a1....

Any ideas...?

Thanks..

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[tony h]

Unfortunately this is not straightforward.

isdate(range) will return a true or false depending on whether the cell
contents look like a date.

But remember a date in excel is only a format applied to a number. so
from the data it is not possible to distinguish between a date and a
number. There is a further confusion possible in that you can have text
that looks like a date but is not a number.

hope this helps

 

[VBA Noob]

This formula checks to see if cell fomatted as d-mmm-yy or dd-mmm-yy

See cell help for more examples.

it will still show "Y" if it looks like a date e.g '17/06/07 will still
return "Y"

=IF(CELL("format",A4)="D1","Y","")

VBA Noob

 

[NickHK]

As Tony explained, it depends on what you consider a date.
If you are happy to allow VBA to decide:
Public Function IsValueDate(argRange As Range) As Boolean
Dim tempDate As Date
On Error Resume Next
tempDate = argRange.Value
IsValueDate = (Err.Number = 0)
End Function

But you will see that this will produce some expected result.

NickHK

 

[Dave Peterson]

But there are other date formats.

maybe...

=IF(LEFT(CELL("format",A4),1)="D","Y","n")

And I think I'd check for a number, too. I can type text in a cell that's
formatted as a date.

=IF(AND(ISNUMBER(A4),LEFT(CELL("format",A4),1)="D"),"Y","n")

And to the OP, be aware that if you change the format of A4 (to General, say),
then the formula won't evaluate until the next recalculation.

 

[Darin Kramer]

Hi there,

Cant seem to get your formulae to work, what does the "D1"
represent....?

Kind regards

DArin





*** Sent via Developersdex http://www.developersdex.com ***

 

[Dave Peterson]

Excel's help is not always bad <vbg>:

If the Microsoft Excel format is CELL returns
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"