You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100, 0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900, 730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub

--

Dave Peterson