[SOLVED] 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

"Darin Kramer" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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 ***

[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.

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

[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"


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