Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time sheet to calculate 2 different columns | Excel Worksheet Functions | |||
Formatting / Time | Excel Worksheet Functions | |||
unmet challenge | Excel Worksheet Functions | |||
Time formatting | Excel Discussion (Misc queries) | |||
Time formatting | Excel Worksheet Functions |