Output format of time

[ttamers]

I have an excel sheet that has information in the following format
[TABLE="width: 500"]
<tbody>[TR]
[TD]09/17/14[/TD]
[TD]5:58a[/TD]
[TD]11:31a[/TD]
[/TR]
[TR]
[TD]09/17/14[/TD]
[TD]12:02p[/TD]
[TD]2:34p[/TD]
[/TR]
</tbody>[/TABLE]

What formula can I use so the output is an AM / PM - please note there is NO space between the time and the a or the p.
This is how I would like the sheet to look - I will also be formating this sheet further so this needs to be standard times.
[TABLE="width: 500"]
<tbody>[TR]
[TD]09/17/14[/TD]
[TD]5:58 am[/TD]
[TD]11:31 am[/TD]
[/TR]
[TR]
[TD]09/17/14[/TD]
[TD]12:02 pm[/TD]
[TD]2:34 pm[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Typically it's best to use serial time values instead of text strings that that looks like time values. Then formatting can be applied to the cells to display the time in your desired format.

That allows you to more easily perform mathematical operations using those values. Assuming your current values are simply text strings, the formula below could be used to convert them to serial time values.

Both B4 and B7 use the same formula to convert the text string in B1 to a serial time value.
Range B7:C8 has had this Custom Formatting applied:
h:mm a/p"m"

Excel 2013
	A	B	C
1	9/17/2014	5:58a	11:31a
2	9/17/2014	12:02p	2:34p
3
4	9/17/2014	0.248611	0.479861
5	9/17/2014	0.501389	0.606944
6
7	9/17/2014	5:58 am	11:31 am
8	9/17/2014	12:02 pm	2:34 pm
Sheet1

Cell Formulas
Range	Formula
B4	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"a"," a"),"p"," p"))
B7	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"a"," a"),"p"," p"))

 

[ttamers]

Thank you worked perfect!

Hi and Welcome to MrExcel,

Typically it's best to use serial time values instead of text strings that that looks like time values. Then formatting can be applied to the cells to display the time in your desired format.

That allows you to more easily perform mathematical operations using those values. Assuming your current values are simply text strings, the formula below could be used to convert them to serial time values.

Both B4 and B7 use the same formula to convert the text string in B1 to a serial time value.
Range B7:C8 has had this Custom Formatting applied:
h:mm a/p"m"

Excel 2013

	A	B	C
5:58a	11:31a
12:02p	2:34p

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]9/17/2014[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]9/17/2014[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD="bgcolor: #FFFF00, align: right"]0.248611[/TD]
[TD="align: right"]0.479861[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD="align: right"]0.501389[/TD]
[TD="align: right"]0.606944[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD="bgcolor: #FFFF00, align: right"]5:58 am [/TD]
[TD="align: right"]11:31 am [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9/17/2014[/TD]
[TD="align: right"]12:02 pm [/TD]
[TD="align: right"]2:34 pm [/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"a"," a"),"p"," p"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"a"," a"),"p"," p"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]