software

I am exporting a report from Crystal Reports because the time quot;inquot; and
time quot;outquot; fields are recognized as text fields in instead of time
fields. I am trying to get a total time. If I try to format the cells
as time cells it has no effect. Here is my example:

11:30 AM 12:30 PM

I use the =Left(a3,5) formula to drop the AM amp; PM off of the cell and I
am able to sucessfully get a time difference for all times that do not
cross the noon hour. I get the following result for times that do
cross noon:

11:30 12:30 ########################

where ########### should actually be 1:00

I realize the root problem is that the database we are pulling from
does not recognize the fields that have times as time fields. I am not
able to correct that so I hope Excel can help with this problem.

Thanks

Mark--
Mark@Marc
------------------------------------------------------------------------
Mark@Marc's Profile: www.excelforum.com/member.php...oamp;userid=28126
View this thread: www.excelforum.com/showthread...hreadid=513665You could encourage Excel to change the text fields into proper times. Copy
a blank cell, then select your 'times', and Edit gt; Paste Special, select Add
and click ok. Adding 0 will convince Excel to convert the text fields into
numbers (when possible). Now just reformat the cells as times (Format gt;
Cells, ...).
You should be able to subtract one time from another format the result as a
time, and see the 1:00.

quot;Mark@Marcquot; wrote:

gt;
gt; I am exporting a report from Crystal Reports because the time quot;inquot; and
gt; time quot;outquot; fields are recognized as text fields in instead of time
gt; fields. I am trying to get a total time. If I try to format the cells
gt; as time cells it has no effect. Here is my example:
gt;
gt; 11:30 AM 12:30 PM
gt;
gt; I use the =Left(a3,5) formula to drop the AM amp; PM off of the cell and I
gt; am able to sucessfully get a time difference for all times that do not
gt; cross the noon hour. I get the following result for times that do
gt; cross noon:
gt;
gt; 11:30 12:30 ########################
gt;
gt; where ########### should actually be 1:00
gt;
gt; I realize the root problem is that the database we are pulling from
gt; does not recognize the fields that have times as time fields. I am not
gt; able to correct that so I hope Excel can help with this problem.
gt;
gt; Thanks
gt;
gt; Mark
gt;
gt;
gt; --
gt; Mark@Marc
gt; ------------------------------------------------------------------------
gt; Mark@Marc's Profile: www.excelforum.com/member.php...oamp;userid=28126
gt; View this thread: www.excelforum.com/showthread...hreadid=513665
gt;
gt;