Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
=IF(A6--"12:00",TEXT(A6-"12:00","h:mm"),TEXT(A6-"0:00","h:mm"))
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
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 "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
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 "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co
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. -- David Biddulph "Mic" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting format of hh:mm AM/PM to only hh:mm non 24hr in sam
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating 24hr Clock, but not as time!! | Excel Discussion (Misc queries) | |||
Do not convert 24hr time in Excel to Portugese postal code | Excel Discussion (Misc queries) | |||
how do I round hours worked to the next half hour with a 24hr shif | Excel Worksheet Functions | |||
NEED A FORMULA TO CALCULATE DIFFERENCE BETWEEN TWO TIMES IN 24HR . | New Users to Excel | |||
24hr moving average | Excel Discussion (Misc queries) |