Time Field Formatting
 

I am exporting a report from Crystal Reports because the time "in" and
time "out" 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 & 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: http://www.excelforum.com/member.php...o&userid=28126
View this thread: http://www.excelforum.com/showthread...hreadid=513665

Time Field Formatting
 

You could encourage Excel to change the text fields into proper times. Copy
a blank cell, then select your 'times', and Edit 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
Cells, ...).
You should be able to subtract one time from another format the result as a
time, and see the 1:00.

"Mark@Marc" wrote:


I am exporting a report from Crystal Reports because the time "in" and
time "out" 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 & 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: http://www.excelforum.com/member.php...o&userid=28126
View this thread: http://www.excelforum.com/showthread...hreadid=513665