Formatting short times

[Walter Briscoe]

I run VBA in Microsoft Visual Basic 6.5.1053 from Microsoft Office Excel
2003 (11.8332.8333) SP3 from Windows Vista Business Service Pack 2.

I have string data representing minutes and seconds. e.g. "2:30",
"12:00", "25:00"

When I write to a cell with an instruction like activecell = S, the data
is allocated a format from my list of custom formats, either "hh:mm" or
"hh:mm:ss". While I am not bothered that "2:30" is rendered as "02:30",
I am that "25:00" is rendered as "25:00:00".

When I tried activecell = Format(S, "mm:ss"), "12:00" is always
displayed and "hh:mm" is the selected custom format.

I would appreciate code which will satisfy my specific need.
i.e. a format string which renders coherently or otherwise.

I would also appreciate pointers to good documentation, which explains
how cell = value is rendered.

Thanks.

 

[joeu2004]

Excel always interprets input of the form ab:cd as hh:mm.

Excel always interprets input of the form ab:cd.e ("e" represents one or
more digits) as mm:ss.fff, where "f" is a fraction of a second rounded to
1/1000-th.

So if string variable t contains time in the form mm:ss, you can do the
following:

ActiveCell = "0:" & t
ActiveCell.NumberFormat = "mm:ss"

If string variable t might contain time in either the form mm:ss or h:mm:ss,
you can do the following:

Dim p As Long
p = InStr(t, ":"): p = InStr(p+1, t, ":")
If p = 0 Then ActiveCell = "0:" & t Else ActiveCell = t
ActiveCell.NumberFormat = "mm:ss"


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

 

[Walter Briscoe]

Excel always interprets input of the form ab:cd as hh:mm.

Excel always interprets input of the form ab:cd.e ("e" represents one
or more digits) as mm:ss.fff, where "f" is a fraction of a second
rounded to 1/1000-th.

So if string variable t contains time in the form mm:ss, you can do the
following:

ActiveCell = "0:" & t

Place the correct value in the cell.

ActiveCell.NumberFormat = "mm:ss"

Display it in the desired format.

If string variable t might contain time in either the form mm:ss or
h:mm:ss, you can do the following:

Dim p As Long
p = InStr(t, ":"): p = InStr(p+1, t, ":")
If p = 0 Then ActiveCell = "0:" & t Else ActiveCell = t
ActiveCell.NumberFormat = "mm:ss"

I don't need to cater for this case.
I am happy with the earlier code. Thank you.