Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting short times
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. -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting short times
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" wrote in message ... 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. -- Walter Briscoe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting short times
In message of Mon, 17 Oct 2011 00:52:57 in
microsoft.public.excel.programming, joeu2004 writes 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. ----- original message ----- "Walter Briscoe" wrote in message ... 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. -- Walter Briscoe -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Having a problem formatting short time | New Users to Excel | |||
Short Date Formatting | Excel Discussion (Misc queries) | |||
Despite formatting a column in Excel 2002 worksheet as Short Date. | Excel Discussion (Misc queries) | |||
Formatting times | Excel Programming | |||
Formatting times | Excel Programming |