Re: IsDate Function
From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 08/22/04
- Next message: Rob Bovey: "Re: Mimick MS Progress Indicator?"
- Previous message: Harald Staff: "Re: list of extended properties for use with ADO connection strings"
- In reply to: Don Lloyd: "Re: IsDate Function"
- Next in thread: Jim Cone: "Re: IsDate Function"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 22 Aug 2004 20:53:52 +0200
Hi Don
but this result is as expected. Your invalid dates are all recognized
as 'strings'. Otherwise you would see the mont name. So the function
=ISNUMBER(cell_reference)
should return FALSE for these values (and ISTEXT(...) TRUE)
-- Regards Frank Kabel Frankfurt, Germany Don Lloyd wrote: > Hi Tom and Jim, > I've entered and tried the following in a blank worksheet. > The target cell format is dd/mmm/yy > > Private Sub Worksheet_Change(ByVal Target As Range) > If IsDate(Target) = False Then > MsgBox "Not a valid Date" > End If > End Sub > > Entry 30/02/03 - No message, cell display 30/02/03 > Entry 28/02/03 - No message, cell display 28/Feb/2003 > Entry 03/13/04 - No message, cell display 03/13/04 > Entry 03/14/04 - No message, cell display 03/14/04 > Entry 03/13/2004 - No message, cell display 03/13/2004 > Entry 03/12/04 - No message, cell display 03/Dec/2004 > > ItThe same results apply using - as a separator. > > Regards, > Don > > > "Tom Ogilvy" <twogilvy@msn.com> wrote in message > news:uTKgvHGiEHA.3932@TK2MSFTNGP09.phx.gbl... >> Don, >> When you enter one of those dates, does the cell display a valid >> date. I suspect yes it does. Therefore, it meets the definition >> that it could be interpreted as a date. >> >> Excel provides robust capabilities to do math with dates. So >> something like Date(2004,13,1) would be Jan 1, 2005. The number >> 20345 is a valid date (Feb 3, 1963). There are many behaviors in >> Excel that don't match people's expectations - but there is usually >> (not always) a reason the behavior is that way. >> >> -- >> Regards, >> Tom Ogilvy >> >> "Don Lloyd" <don.lloyd2@virgin.net> wrote in message >> news:Ca2Wc.482$lx4.56@newsfe6-gui.ntli.net... >>> Thank you Frank, DBAL and Norman >>> >>> Apologies for quoting 1/100/04 as an untrapped value, which it >>> isn't. >>> >>> I am rather surprised by the fact that the IsDate Function does not >>> regard for example, 30/02/04 and 03/13/04 as invalid dates. Even I >>> know that ! >>> >>> While it is possible to employ workarounds (thank you for your >>> suggestions) these are rather complex for what they achieve and in >>> this particular instance I will resort to using the Validation >>> function. I don't like the imposed roadworks signs, which don't >>> mean much to the average user, but it works and beggars can't be >>> choosers. >>> >>> I think the lesson to be learned is that those of us who are less >>> well informed should not implicity accept the claimed property of a >>> function as infallible >>> >>> Quote: >>> "IsDate returns True if the expression is a date or is recognizable >>> as a valid date; otherwise, it returns False." >>> >>> The examples quoted (there are many others) are NOT dates, but the >>> function returned True >>> >>> Good, having got that off my chest I'm away to take it out on a >>> golf ball. Watch out Tiger ! >>> >>> Regards, >>> Don >>> >>> >>> >>> "Don Lloyd" <don.lloyd2@virgin.net> wrote in message >>> news:%1UVc.1808$tc5.1511@newsfe6-gui.ntli.net... >>>> Hi, >>>> >>>> Excel 97, Excel 2003 >>>> cell format dd/mm/yy >>>> >>>> Q. >>>> In the Worksheet Change routine I have the following code >>>> >>>> If IsDate(Target) = False Then >>>> MsgBox >>>> End If >>>> >>>> Entries such as 32/10/04, 1/100/04 are quite happily accepted as >>>> dates. Advice gratefully appreciated. >>>> >>>> If possible I would rather trap with code rather than using data >>>> validation. >>>> >>>> Thanks for any assistance >>>> >>>> Don
- Next message: Rob Bovey: "Re: Mimick MS Progress Indicator?"
- Previous message: Harald Staff: "Re: list of extended properties for use with ADO connection strings"
- In reply to: Don Lloyd: "Re: IsDate Function"
- Next in thread: Jim Cone: "Re: IsDate Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
