|
IsDate Function
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 |
IsDate Function
Hi
quite simple :-) Excel thinks these are valid dates (and somehow is correct with that...). You can test this if you use msgbox datevalue(target.value) so for example: 32/10/04: interepreted as YY/MM/DD and that is 04-Oct-1932 Your second example returns FALSE for me (but this could be something with regional settings). Try this value with DateValue. what does it return. In total you can't do much against it if you use ISDATE. You could check the individual parts of your entered string -- Regards Frank Kabel Frankfurt, Germany Don Lloyd wrote: 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 |
IsDate Function Perfected!
Don, this will force the user to enter a date in the
following format only MM/DD/YYYY. Here you go buddy: 'Isdate Perfected for Date Format MM/DD/YYYY by DBAL 'Get Date From User Do varDate = InputBox(prompt:="Enter the Date (MM/DD/YYYY).", Title:="DATE") If varDate = "" Then Exit Sub ElseIf Not IsDate(varDate) Then MsgBox "You Must Enter A Valid Start Date" ElseIf Len(varDate) < 10 Or Left(varDate, 2) 12 Or Mid(varDate, 4, 2) 31 Then MsgBox "Your Entry Was " & varDate & "." & Chr (13) & " Please Use This Date Format: (MM/DD/YYYY)." End If Loop Until IsDate(varDate) = True And Len(varDate) = 10 And Left(varDate, 2) <= 12 And Mid(varDate, 4, 2) <= 31 MsgBox "This Date Is Of The Proper Format (MM/DD/YYYY): " & varDate Best Regards, DBAL kungfoocomputers.com -----Original Message----- 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 . |
IsDate Function
Hi Don,
You could adapt the Worksheet Change routine using information returned by the Worksheet Day function, which does not behave in the same way as the corresponding VBA function. The following event procedure temporarily seconds a specified cell - empty or not - to interrogate the target cell. In the code, this cell is set to A1. You can change this to any cell which is NOT to receive a date entry: I have only tested this with your specified non-US date formats. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim Rng As Range Dim OldVal As Variant Set Rng = Range("A1") '<<< CHANGE TO SUIT OldVal = Rng.Formula With Application .ScreenUpdating = False End With On Error GoTo CleanUp If Intersect(Rng, Target) Is Nothing Then Application.EnableEvents = False Rng.Formula = "=Day(" & Target.Address & ")" If IsError(Rng.Value) Then MsgBox "Invalid date!" End If Rng.Formula = OldVal Else Exit Sub End If CleanUp: Rng.Formula = OldVal With Application .EnableEvents = True .ScreenUpdating = True End With End Sub --- Regards, Norman "Don Lloyd" wrote in message ... 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 |
IsDate Function
Don,
Are your sure that those non-dates are not being seen as "time"? (values less than one) Regards, Jim Cone San Francisco, CA - snip - |
IsDate Function
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" wrote in message ... 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 |
IsDate Function
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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
Don,
I assume you want to limit the user entry to certain cells and also to expect a date entry within a certain time span. (the component parts are due sometime this year not ten years from now) The following only checks the entry in 6 cells in column B and the date must be within plus or minus approx. 6 months. Maybe something similar will work for you... '--------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("B5:B10")) Is Nothing Then If Not IsDate(Target) Or _ Target.Value < (Date - 180) Or _ Target.Value (Date + 180) Then MsgBox "Not a valid Date" End If End If End Sub '------------------------------------------------------- Regards, Jim Cone San Francisco, CA "Don Lloyd" wrote in message ... 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 - snip - |
IsDate Function
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
? isdate("30/02/03")
True ? datevalue("30/02/03") 2/3/30 ? isdate("28/Feb/2003") True ? dateValue("28/Feb/2003") 2/28/03 ? isdate("03/13/04") True ? datevalue("03/13/04") 3/13/04 ? isdate("03/14/04") True ? datevalue("03/14/04") 3/14/04 ? isdate("03/13/2004") True ? datevalue("03/13/2004") 3/13/04 ? isdate("03/12/04") True ? datevalue("03/12/04") 3/12/04 They can all be interpreted as a date in VBA. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
for the second one:
? isdate("28/02/03") True ? datevalue("28/02/03") 2/3/28 "Tom Ogilvy" wrote in message ... ? isdate("30/02/03") True ? datevalue("30/02/03") 2/3/30 ? isdate("28/Feb/2003") True ? dateValue("28/Feb/2003") 2/28/03 ? isdate("03/13/04") True ? datevalue("03/13/04") 3/13/04 ? isdate("03/14/04") True ? datevalue("03/14/04") 3/14/04 ? isdate("03/13/2004") True ? datevalue("03/13/2004") 3/13/04 ? isdate("03/12/04") True ? datevalue("03/12/04") 3/12/04 They can all be interpreted as a date in VBA. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
Hi again,
Thanks for your feedback and apologies for dragging the thread on for so long. It may help if I explain better what I am aiming for. The user enters a date into a fixed cell - the program then generates a list of up to about 40 consecutive weekly dates (depending on the value of another cell), starting with the original entry. This works perfectly if a valid date is entered. I set up the IsDate function system some time ago and it appeared to work well, until I as a matter of routine, it was tested with some invalid dates. It came as a surprise, to me, that it failed to detect these.: Quote from help "IsDate Function Returns a Boolean value indicating whether an expression can be converted to a date. Syntax IsDate(expression) The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time " Unquote From the cell display I would think that Excel would treat the date entered as string expression. A further point - cell Validation instantly recognises the example dates as being invalid. However (I need to check further on this) Validation allows acceptance of an invalid entry in some cases, and the program will then fail on the next stage. Thanks again for your help - I'll get there eventually ! Regards, Don "Tom Ogilvy" wrote in message ... for the second one: ? isdate("28/02/03") True ? datevalue("28/02/03") 2/3/28 "Tom Ogilvy" wrote in message ... ? isdate("30/02/03") True ? datevalue("30/02/03") 2/3/30 ? isdate("28/Feb/2003") True ? dateValue("28/Feb/2003") 2/28/03 ? isdate("03/13/04") True ? datevalue("03/13/04") 3/13/04 ? isdate("03/14/04") True ? datevalue("03/14/04") 3/14/04 ? isdate("03/13/2004") True ? datevalue("03/13/2004") 3/13/04 ? isdate("03/12/04") True ? datevalue("03/12/04") 3/12/04 They can all be interpreted as a date in VBA. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
and as I showed you, VBA can interpret these as valid date - perhaps not the
date intended, but it successfully interpreted each one of these. Some you are seeing as invalid simply because they are in a US format. However, VBA works in a US format. It will interpret the date as dd/mm/yy only if it is unsuccessful treating it as US. Also, from the tests, it is clear that some invalid entries in the dd portion makes it interpret it as yy/mm/dd. I only restate the above, because you appear to be missing the point that the function is working exactly as the explanation you quote states. You seem to want it to see dates the way you see them, but unfortunately that is not the case. How excel itself works and how VBA works are two different situations. In many cases, Excel works from one set of rules and VBA from another. Sometimes they are close and sometimes not. VBA is a separate product and has been Wed to Excel (and powerpoint and word and access and autocad, etc). But they are all developed by different groups and are not totally consistent on the rules they use. Another related example is if you import a CSV file with dates in dd/mm/yy format in your version of excel, they will come in fine. If you record a macro at the same time and run the macro, the dates will be screwed up because of the US centric nature of VBA. This is true in Excel 97 and 2000 for sure. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... Hi again, Thanks for your feedback and apologies for dragging the thread on for so long. It may help if I explain better what I am aiming for. The user enters a date into a fixed cell - the program then generates a list of up to about 40 consecutive weekly dates (depending on the value of another cell), starting with the original entry. This works perfectly if a valid date is entered. I set up the IsDate function system some time ago and it appeared to work well, until I as a matter of routine, it was tested with some invalid dates. It came as a surprise, to me, that it failed to detect these.: Quote from help "IsDate Function Returns a Boolean value indicating whether an expression can be converted to a date. Syntax IsDate(expression) The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time " Unquote From the cell display I would think that Excel would treat the date entered as string expression. A further point - cell Validation instantly recognises the example dates as being invalid. However (I need to check further on this) Validation allows acceptance of an invalid entry in some cases, and the program will then fail on the next stage. Thanks again for your help - I'll get there eventually ! Regards, Don "Tom Ogilvy" wrote in message ... for the second one: ? isdate("28/02/03") True ? datevalue("28/02/03") 2/3/28 "Tom Ogilvy" wrote in message ... ? isdate("30/02/03") True ? datevalue("30/02/03") 2/3/30 ? isdate("28/Feb/2003") True ? dateValue("28/Feb/2003") 2/28/03 ? isdate("03/13/04") True ? datevalue("03/13/04") 3/13/04 ? isdate("03/14/04") True ? datevalue("03/14/04") 3/14/04 ? isdate("03/13/2004") True ? datevalue("03/13/2004") 3/13/04 ? isdate("03/12/04") True ? datevalue("03/12/04") 3/12/04 They can all be interpreted as a date in VBA. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
IsDate Function
Thank you Tom for your patience and time in explaining the reasons behind
the problem. I'm based in the UK - Wales in fact - which is as good a reaon as any for using UK formats ! I trust that other persons frequenting this newsgroup who are not using US formats will take note. It therefore appears that I must either a) Employ Excel's strongest Validation procedure or b) more likely, use an error trapping routine when the invalidity is recognised. Once again, thanks for all your help. Regards, Don "Tom Ogilvy" wrote in message ... and as I showed you, VBA can interpret these as valid date - perhaps not the date intended, but it successfully interpreted each one of these. Some you are seeing as invalid simply because they are in a US format. However, VBA works in a US format. It will interpret the date as dd/mm/yy only if it is unsuccessful treating it as US. Also, from the tests, it is clear that some invalid entries in the dd portion makes it interpret it as yy/mm/dd. I only restate the above, because you appear to be missing the point that the function is working exactly as the explanation you quote states. You seem to want it to see dates the way you see them, but unfortunately that is not the case. How excel itself works and how VBA works are two different situations. In many cases, Excel works from one set of rules and VBA from another. Sometimes they are close and sometimes not. VBA is a separate product and has been Wed to Excel (and powerpoint and word and access and autocad, etc). But they are all developed by different groups and are not totally consistent on the rules they use. Another related example is if you import a CSV file with dates in dd/mm/yy format in your version of excel, they will come in fine. If you record a macro at the same time and run the macro, the dates will be screwed up because of the US centric nature of VBA. This is true in Excel 97 and 2000 for sure. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... Hi again, Thanks for your feedback and apologies for dragging the thread on for so long. It may help if I explain better what I am aiming for. The user enters a date into a fixed cell - the program then generates a list of up to about 40 consecutive weekly dates (depending on the value of another cell), starting with the original entry. This works perfectly if a valid date is entered. I set up the IsDate function system some time ago and it appeared to work well, until I as a matter of routine, it was tested with some invalid dates. It came as a surprise, to me, that it failed to detect these.: Quote from help "IsDate Function Returns a Boolean value indicating whether an expression can be converted to a date. Syntax IsDate(expression) The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time " Unquote From the cell display I would think that Excel would treat the date entered as string expression. A further point - cell Validation instantly recognises the example dates as being invalid. However (I need to check further on this) Validation allows acceptance of an invalid entry in some cases, and the program will then fail on the next stage. Thanks again for your help - I'll get there eventually ! Regards, Don "Tom Ogilvy" wrote in message ... for the second one: ? isdate("28/02/03") True ? datevalue("28/02/03") 2/3/28 "Tom Ogilvy" wrote in message ... ? isdate("30/02/03") True ? datevalue("30/02/03") 2/3/30 ? isdate("28/Feb/2003") True ? dateValue("28/Feb/2003") 2/28/03 ? isdate("03/13/04") True ? datevalue("03/13/04") 3/13/04 ? isdate("03/14/04") True ? datevalue("03/14/04") 3/14/04 ? isdate("03/13/2004") True ? datevalue("03/13/2004") 3/13/04 ? isdate("03/12/04") True ? datevalue("03/12/04") 3/12/04 They can all be interpreted as a date in VBA. -- Regards, Tom Ogilvy "Don Lloyd" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com