Home |
Search |
Today's Posts |
#1
|
|||
|
|||
convert time imported as text to time format for calculations
I imported some data into excel that is in a dbf file. two of the columns
contain time information expressed in the military format (No : separating the hours and minutes). When I calculate the difference, the answer is in base 100 format ie from 1300 hours to 1400 hours is 100 units, not 60. How do I either convert these cells into a time format that will properly format the answer or take the answer given and have it make sense. ANything less than 1 hour reads okay, anything over is not. |
#2
|
|||
|
|||
convert time imported as text to time format for calculations
I would create a dummy column and insert
=(60*VALUE(LEFT(TEXT(A1,"0000"),2))+VALUE(RIGHT(A1 ,2)))/60/24 and then format the row as time. You can then copy and paste special selecting values to remove the formula. Don't forget to format as time again where you pasted. You can then delete all unecessary columns. "batfish" wrote: I imported some data into excel that is in a dbf file. two of the columns contain time information expressed in the military format (No : separating the hours and minutes). When I calculate the difference, the answer is in base 100 format ie from 1300 hours to 1400 hours is 100 units, not 60. How do I either convert these cells into a time format that will properly format the answer or take the answer given and have it make sense. ANything less than 1 hour reads okay, anything over is not. |
#3
|
|||
|
|||
convert time imported as text to time format for calculations
=TIME(LEFT(TEXT(A1,"0000"),2), RIGHT(A1,2), 0)
should convert a 3 or 4 character military time value into a "proper" Excel time value. If you only need to convert 4 character values (i.e., leading zeros always supplied) then you can simplify it to: =TIME(LEFT(A1,,2), RIGHT(A1,2), 0) Anything less than 1 hour reads okay, anything over is not. Are you 100% sure about that? Does 1400 minus 1315 gives you 85 or 45? HTH, -- George Nicholson Remove 'Junk' from return address. "batfish" wrote in message ... I imported some data into excel that is in a dbf file. two of the columns contain time information expressed in the military format (No : separating the hours and minutes). When I calculate the difference, the answer is in base 100 format ie from 1300 hours to 1400 hours is 100 units, not 60. How do I either convert these cells into a time format that will properly format the answer or take the answer given and have it make sense. ANything less than 1 hour reads okay, anything over is not. |
#4
|
|||
|
|||
convert time imported as text to time format for calculations
Both of these worked, except when the ime interval went past midnight. I
have the date information in another colum and know there is a way to join the two cells, but can't remember what it is... Concatenate provide a jibberish number "George Nicholson" wrote: =TIME(LEFT(TEXT(A1,"0000"),2), RIGHT(A1,2), 0) should convert a 3 or 4 character military time value into a "proper" Excel time value. If you only need to convert 4 character values (i.e., leading zeros always supplied) then you can simplify it to: =TIME(LEFT(A1,,2), RIGHT(A1,2), 0) Anything less than 1 hour reads okay, anything over is not. Are you 100% sure about that? Does 1400 minus 1315 gives you 85 or 45? HTH, -- George Nicholson Remove 'Junk' from return address. "batfish" wrote in message ... I imported some data into excel that is in a dbf file. two of the columns contain time information expressed in the military format (No : separating the hours and minutes). When I calculate the difference, the answer is in base 100 format ie from 1300 hours to 1400 hours is 100 units, not 60. How do I either convert these cells into a time format that will properly format the answer or take the answer given and have it make sense. ANything less than 1 hour reads okay, anything over is not. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert [h]:mm sum total format to number format | Excel Worksheet Functions | |||
Everytime I need to format cells or text, Excel 2003 takes a lot of time or free | Excel Discussion (Misc queries) | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) | |||
Help Q: Entering 7 p time format, does not convert to 19:00 | Excel Discussion (Misc queries) | |||
Convert text file to MS_Excel | Excel Discussion (Misc queries) |