I'm assuming your original times are numeric. You could try selecting the
cells you want changed and running this macro. Back up your data before
trying.

Sub test()
Dim rngCell As Range

Selection.NumberFormat = "@"
For Each rngCell In Selection.Cells
rngCell.Value = Application.Text(rngCell.Value - _
IIf(Hour(rngCell.Value) 12, 0.5, 0), "hh:mm")
Next rngCell

End Sub


"Mic" wrote:

Hey thanks for all of your hard work.
Unfortunately this does not answer my question, albeit genous.
These answers both put them into a seperate column, and one of them just
hides the AM/PM.

Just incase you think of something, I will re-do the question, but I really
don't think that there is a solution.

Question: I am trying to mimic an import of a .Dat File using an FTP were
the Time is FTP as Text, and is in the formate of 08:30. Unfortunately the
system that is generating the .Dat file does not have the leading 0.
Please do not include AM/PM, or have it hidden. Nor in a different column,
with a formula. This data needs to be imported into a seperate system, and a
formula will not import.

I really doubt that there is a way to do this, but thanks for your guys hard
work.
Mic

"Mic" wrote:

Is there a way to:

Take a column that is formated to hh:mm AM/PM to remove the am or pm in the
same column (Think that it would have to be text)

Or

Start with Text that 8:30 and make it 08:30 without the AM/PM

Mic