Date conversion doesn't make sense

[merlynknight]

I am having problems with converting text to date using the cdate
function
When I convert .5 into a format of HH:MM AM/PM it becomes 12:00 PM
which makes sense because noon is half of a day

When I convert 0.5 into a format of HH:MM AM/PM it becomes 12:05 AM
which doesn't make sense

When I convert 0.50 into a format of HH:MM AM/PM it becomes 12:50 AM
which also doesn't make sense

When I convert 0.500 into a format of HH:MM AM/PM it becomes 12:00 PM

Below is a msgbox that demonstrates this.
What is happening?

Sub test1()
MsgBox "The string "".5"" converts to " & Format(CDate(".5"), "MMM
dd yyyy HH:MM AM/PM") _
& vbLf & "The string ""0.5"" converts to " & Format(CDate("0.5"),
"MMM dd yyyy HH:MM AM/PM") _
& vbLf & "The string ""0.50"" converts to " &
Format(CDate("0.50"), "MMM dd yyyy HH:MM AM/PM") _
& vbLf & "The string ""0.500"" converts to " &
Format(CDate("0.500"), "MMM dd yyyy HH:MM AM/PM")
End Sub

Thanks for any help
Merlyn

 

[JoeU2004]

I am having problems with converting text
to date using the cdate function
When I convert .5 into a format of HH:MM AM/PM

But that is not what you are doing. You are converting the result of CDate
into a date or time format.

I think you would get some insight into your mistake if you simply did
MsgBox CDate(...), where "..." is ".5", "0.5", "0.50" and "0.500".

Seeing the presumably unexpected results of CDate, you might be motivated to
read the Help page for CDate (actually Type Conversion Functions).

There, you will see that while the result of CDate is a date/time serial
number, the parameter should be a "date expression", which is defined as:

"Any expression that can be interpreted as a date, including date literals,
numbers that look like dates, strings that look like dates, and dates
returned from functions. A date expression is limited to numbers or strings,
in any combination, that can represent a date from January 1, 100 - December
31, 9999."

IMHO, ".5" et al do not fit the definition of a "date expression". I'm
surprised it does not simply result in an error.

Bottom line: I think you want to do simply Format(.5,"hh:mm AM/PM"),
Format(0.5,"hh:mm AM/PM"), etc. Note that there are no quotes around .5,
0.5 et al. So obviously they will all convert to the same formatted time,
namely 12:00 PM.

If you are starting with the string ".5" etc, you can write
Format(--".5","hh:mm AM/PM"). The "--" (double negative) arithmetic
operation has the effect of converting the string to the equivalent number.


----- original message -----

 

[JoeU2004]

Errata....

I think you would get some insight into your
mistake if you simply did MsgBox CDate(...)

I meant:

Dim x as Double
x = CDate("..."): MsgBox x

where "..." is ".5", "0.5", "0.50" and "0.500".


----- original message -----

 

[Peter T]

Change
CDate(".5")
to
CDate(Val(".5"))

or even don't use CDate at all, only use Val()

Reason is CDate assumes user the string is already 'like' a time, rather
than an ordinary number

Regards,
Peter T