Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co

[Guest]

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

 

[Guest]

=IF(A6>--"12:00",TEXT(A6-"12:00","h:mm"),TEXT(A6-"0:00","h:mm"))

 

[Guest]

Format/Cell/Custom hh:mm

But 8:30 PM will display as 20:30. If you want 08:30 for both AM and PM,
try this in an adjacent column (where A2 contains your time)

=A2-(HOUR(A2)>12)*0.5

and format this column hh:mm

 

[Guest]

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

 

[Guest]

If there was a way to have a formula that split, or moved the am/pm to
another column, while keeping only the Numeric value in the first column.

Or if I could Export the data to Word or another program, format it from
there, and re-FTP and save it under a new name.

Just thoughts

 

[David Biddulph]

If the input data are in the format of 8:30, without the leading zero (and
without the AM/PM), then you can merely format the cell as hh:mm.

If your input data include the AM/PM, then you can't remove that simply by
formatting. You'll need either to import as time and change the value, or
import as text and manipulate the text string, but you can't change it
simply by cell formatting.

Someone may come up with a VB solution.

 

[Guest]

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