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.

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.

=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" <[email protected]> wrote in message
news:[email protected]...
>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.

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>