software

I receive a text file that contains several quot;timequot; entry columns formatted as
text. The cells contain values such as quot;8:30AMquot; (no space between the zero
and quot;Aquot;). 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 quot;Aquot; (or quot;Pquot; for
PM) and I need to format the column as quot;hh:mm a/pquot;. 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,quot;AMquot;,quot; AMquot;),quot;PMquot;,quot;
PMquot;)-SUBSTITUTE(SUBSTITUTE(A1,quot;AMquot;,quot; AMquot;),quot;PMquot;,quot; PMquot;)
--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Lance Bquot; gt; wrote in message
news
gt;I receive a text file that contains several quot;timequot; entry columns formatted
gt;as
gt; text. The cells contain values such as quot;8:30AMquot; (no space between the
gt; zero
gt; and quot;Aquot;). I need to convert these to time formats so that I can subtract
gt; multiple columns from one another.
gt;
gt; The first thing I need to do is put a space between zero and quot;Aquot; (or quot;Pquot;
gt; for
gt; PM) and I need to format the column as quot;hh:mm a/pquot;. I have tried several
gt; methods and none seem to work (I have thousands of cells to process).
gt; Text
gt; to Columns is not an option because there is no delimiter and no exact
gt; fixed
gt; width (for example: some entries are 7:00AM and other are 12:30PM).
gt;
gt; What is the best (and simplest) way to achieve what I'm after?
gt;
gt; I hope I've been clear, any help is greatly appreciated.
gt;
gt; Thanks!
That worked perfectly.

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

L

quot;Peo Sjoblomquot; wrote:

gt; Assume end time in B1 and start in A1
gt;
gt; =SUBSTITUTE(SUBSTITUTE(B1,quot;AMquot;,quot; AMquot;),quot;PMquot;,quot;
gt; PMquot;)-SUBSTITUTE(SUBSTITUTE(A1,quot;AMquot;,quot; AMquot;),quot;PMquot;,quot; PMquot;)
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Excel 95 - Excel 2007
gt; Northwest Excel Solutions
gt; www.nwexcelsolutions.com
gt; quot;It is a good thing to follow the first law of holes;
gt; if you are in one stop digging.quot; Lord Healey
gt;
gt;
gt; quot;Lance Bquot; gt; wrote in message
gt; news
gt; gt;I receive a text file that contains several quot;timequot; entry columns formatted
gt; gt;as
gt; gt; text. The cells contain values such as quot;8:30AMquot; (no space between the
gt; gt; zero
gt; gt; and quot;Aquot;). I need to convert these to time formats so that I can subtract
gt; gt; multiple columns from one another.
gt; gt;
gt; gt; The first thing I need to do is put a space between zero and quot;Aquot; (or quot;Pquot;
gt; gt; for
gt; gt; PM) and I need to format the column as quot;hh:mm a/pquot;. I have tried several
gt; gt; methods and none seem to work (I have thousands of cells to process).
gt; gt; Text
gt; gt; to Columns is not an option because there is no delimiter and no exact
gt; gt; fixed
gt; gt; width (for example: some entries are 7:00AM and other are 12:30PM).
gt; gt;
gt; gt; What is the best (and simplest) way to achieve what I'm after?
gt; gt;
gt; gt; I hope I've been clear, any help is greatly appreciated.
gt; gt;
gt; gt; Thanks!
gt;
gt;
gt;