Why does Format(Now, “mm:ss”) fail in VBA erratically whereas the TEXT() worksheet function works?
Microsoft Excel interprets the format “mm:ss” as minutes and seconds while Visual Basic interprets it as months and seconds. This is by design. To display minutes and seconds, Visual Basic requires the format to be specified as “nn:ss”. However, if your format string is “hh:mm:ss”, Visual Basic guesses correctly that you need hours, minutes and seconds! That explains the erratic behavior.
Related Questions
- Im using a VBA function that I wrote, but it executes each time I add or delete a cell on the worksheet. Whats going on?
- Can a custom worksheet function written in VBA perform the same types of actions as a subroutine?
- Why does Format(Now, "mm:ss") fail in VBA erratically whereas the TEXT() worksheet function works?
