Output of Format from a string

Question

Last night I ran into something that drove me nuts for a good ten, fifteen minutes before I figured it out. But I don't understand why it is this way, so I'm hoping someone here can elucidate.

From the Immediate window in the VBA IDE:

?Format(0.5, "HH:MM AM/PM")
12:00 PM
?Format("0.5", "HH:MM AM/PM")
12:05 AM
?Format(CDbl("0.5"), "HH:MM AM/PM")
12:00 PM

From this page I see that 0.5 should correspond to 12:00 PM, since one hour is 0.04166... ((12 * (1/24) = 0.5)). And as you can see, it does if I pass a number to Format() but not if I pass the same number as a string.

Per the info from the same page, 1 minute is 0.00069444... ((1/(24*60))), meaning 12:05 AM should be stored in Excel as 0.003472222 ((0 * (1/24)) + (5 * (1/(24*60)))). And, in fact:

?Format(0.003472222, "HH:MM AM/PM")
12:05 AM

Some more oddities I don't understand:

?Format(2.5, "HH:MM AM/PM")
12:00 PM
?Format("2.5", "HH:MM AM/PM")
02:05 AM

But...

?Format(2.523, "HH:MM AM/PM")
12:33 PM
?Format("2.523", "HH:MM AM/PM")
12:33 PM

Everything I can find in documentation says that "Times are stored as part of a real number. Values to the right of the decimal represent the time. For example, midday (12:00 P.M.) is represented by 0.5." (From Excel 2010's built-in VBE Glossary) So 2.5 should result in a time of 12:00 PM since the part to the right of the decimal is 0.5 and 2.523 should result in 12:33 PM since 0.523 = (12 * (1/24)) + (33 * (1/(24*60))). But VBA only returns the correct result for one of those.

So can anyone explain why VBA exhibits what appears to be inconsistent behavior? Or is there some wrinkle I'm missing?

This was tested in Excel 2010 and 2013.

Answer 1

You expect Format to convert 0.5 to a double, but it doesn't. According to https://msdn.microsoft.com/en-us/library/ee198964.aspx, the String to Date implicit let conversion goes

• If it's a date/time, time, or date (in that order), convert it to a date.
• If it's in the range of a Double, convert it to a double then to a date.
• If neither of those, return an error.

Since it's not converting to a double, it must be converting on the first bullet. And it is. It's assuming "0.5" is a time. According to https://msdn.microsoft.com/en-us/library/dn528865.aspx, a decimal point is a valid time separator.

time-separator = *WSC (":" / ".") *WSC 

Answer 2

The difference is that you're asking VBA to format a Double and a String. You're expecting VBA to interpret the string value the same way as the double value.

If you have a string that represents midnight, it can be 12:00 AM or 00:00 (in 24 hour format). VBA is interpreting the separator as either : or . in the same manner. So for a String formatted to represent five minutes after midnight, you can write 12:05 AM, 00.05, 0.5, 0:05, or 0:5. Also look at the function CDATE.

All of the strings, when converted to Double give the same result:

?Cdbl(CDate("00.05"))
 3.472222222222222E-02