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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com