I receive a text file that contains several "time" entry columns formatted as
text. The cells contain values such as "8:30AM" (no space between the zero
and "A"). I need to convert these to time formats so that I can subtract
multiple columns from one another.

The first thing I need to do is put a space between zero and "A" (or "P" for
PM) and I need to format the column as "hh:mm a/p". I have tried several
methods and none seem to work (I have thousands of cells to process). Text
to Columns is not an option because there is no delimiter and no exact fixed
width (for example: some entries are 7:00AM and other are 12:30PM).

What is the best (and simplest) way to achieve what I'm after?

I hope I've been clear, any help is greatly appreciated.

Thanks!

Assume end time in B1 and start in A1

=SUBSTITUTE(SUBSTITUTE(B1,"AM"," AM"),"PM","
PM")-SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM")



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Lance B" wrote in message
...
I receive a text file that contains several "time" entry columns formatted
as
text. The cells contain values such as "8:30AM" (no space between the
zero
and "A"). I need to convert these to time formats so that I can subtract
multiple columns from one another.

The first thing I need to do is put a space between zero and "A" (or "P"
for
PM) and I need to format the column as "hh:mm a/p". I have tried several
methods and none seem to work (I have thousands of cells to process).
Text
to Columns is not an option because there is no delimiter and no exact
fixed
width (for example: some entries are 7:00AM and other are 12:30PM).

What is the best (and simplest) way to achieve what I'm after?

I hope I've been clear, any help is greatly appreciated.

Thanks!

That worked perfectly.

Thank you so much - you saved me hours of frustration!

L

"Peo Sjoblom" wrote:

Assume end time in B1 and start in A1

=SUBSTITUTE(SUBSTITUTE(B1,"AM"," AM"),"PM","
PM")-SUBSTITUTE(SUBSTITUTE(A1,"AM"," AM"),"PM"," PM")



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Lance B" wrote in message
...
I receive a text file that contains several "time" entry columns formatted
as
text. The cells contain values such as "8:30AM" (no space between the
zero
and "A"). I need to convert these to time formats so that I can subtract
multiple columns from one another.

The first thing I need to do is put a space between zero and "A" (or "P"
for
PM) and I need to format the column as "hh:mm a/p". I have tried several
methods and none seem to work (I have thousands of cells to process).
Text
to Columns is not an option because there is no delimiter and no exact
fixed
width (for example: some entries are 7:00AM and other are 12:30PM).

What is the best (and simplest) way to achieve what I'm after?

I hope I've been clear, any help is greatly appreciated.

Thanks!