Time Field Formatting

  • Thread starter Thread starter Mark@Marc
  • Start date Start date
M

Mark@Marc

I am exporting a report from Crystal Reports because the time "in" an
time "out" fields are recognized as text fields in instead of tim
fields. I am trying to get a total time. If I try to format the cell
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
am able to sucessfully get a time difference for all times that do no
cross the noon hour. I get the following result for times that d
cross noon:

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

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

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

Thanks

Mar
 
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.
 
Back
Top