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 ***

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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=562744

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562744

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.

VBA Noob wrote:

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

--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562744

--

Dave Peterson

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 ***

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"


Darin Kramer wrote:

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

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

"Darin Kramer" wrote in message
...


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 ***