convert time imported as text to time format for calculations

[Guest]

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.

 

[Guest]

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.

 

[George Nicholson]

=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,

 

[Guest]

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