Thread
:
Correcting time input by users
View Single Post
#
10
Posted to microsoft.public.excel.programming
Sandy Mann
external usenet poster
Posts: 2,345
Correcting time input by users
Yes CDate worked fine.
Thanks for the links, I will check them out.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Rick Rothstein (MVP -
VB
)" wrote in
message ...
But the CDate worked in XL97, right?
As for the Replace function, you might be able to use one of the
VB
coded
functions on this page to duplicate the Replace function's capabilities...
http://www.xbeat.net/vbspeed/c_Replace.htm
You can find other
VB
coded functions for the other newer String functions
under the VB6 to VB5 column on this webpage...
http://www.xbeat.net/vbspeed/
Rick
"Sandy Mann" wrote in message
...
Very good Rick. My XL97 doesn't like Replace being used like that but
something to remember when I become posh. <g
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Rick Rothstein (MVP -
VB
)" wrote
in message ...
Also, in thinking about it, instead of this test...
If IsNumeric(Target) Then Exit Sub
maybe this one would be more robust...
If Not IsDate(Target.Value) Then Exit Sub
Rick
"Rick Rothstein (MVP -
VB
)" wrote
in message ...
If Right(UCase(Target.Value), 2) = "AM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) &
" AM"
If Right(UCase(Target.Value), 2) = "PM" Then _
Target.Value = Left(Target.Value, Len(Target.Value) - 2) &
" PM"
I believe you can replace the above code lines with this single line...
Target.Value = CDate(Target.Value)
and it will successfully handle 10:00p and 10:00a also. Of course, it
returns the seconds as well, but the cell can be Custom Formatted to
handle that; or we could just do it in code...
Target.Value = Replace(CDate(Target.Value), ":00 ", " ")
Rick
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann