I get a file that displays time as 0515A. I need to convert it to 05:15 AM.
I use the mid function to retrieve the hour, minutes and A or P. I then
rebuild the string as 05:15 AM and write it to a cell.

Before writing it to a cell, I format the cell as "hh:mm AM/PM".

When I look at the cell, I see 05:15 AM. However, the actual cell value is
05:15:00 AM.

Is there a format or way to make the actual cell value as 05:15 AM instead of
05:15:00 AM?

Thanks for the help......
--
JT

Format as text as you are building a text string.
dm

"JT" wrote:

> I get a file that displays time as 0515A. I need to convert it to 05:15 AM.
> I use the mid function to retrieve the hour, minutes and A or P. I then
> rebuild the string as 05:15 AM and write it to a cell.
>
> Before writing it to a cell, I format the cell as "hh:mm AM/PM".
>
> When I look at the cell, I see 05:15 AM. However, the actual cell value is
> 05:15:00 AM.
>
> Is there a format or way to make the actual cell value as 05:15 AM instead of
> 05:15:00 AM?
>
> Thanks for the help......
> --
> JT

> Format as text as you are building a text string.

....or leave it the way you have it now (as a date), but use the Text
property of the Range object when building a text string in code (VBA); or
use the TEXT function to format it the way you want when building a text
string in a worksheet formula.

--
Rick (MVP - Excel)

Actually when you enter the time as 05:15 AM, for excel the actual
cell value will be 05:15:00 AM as excel assumes that second part is
zero as you have not mentioned it.

I believe, there is nothing wrong if excel shows seconds as zero.