Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it to
miliary time (17:00).

Thanks.

I cannot see how you could do it just with formatting
I am assuming that a time such as 2:45 would be imported as 245 not 275 (45
mins being 0.75 of an hour)
If the value is in A1, then =TIME(A1/100,MOD(A1,100),0)
You could insert a new column; use the formula (changing A1 as needed); then
use copy and paste special as Values so that you could them delete the
original column.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Janet Kreinbrink" <Janet wrote in
message ...
Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it
to
miliary time (17:00).

Thanks.

try using this custom frmat: [h]:[m]

"Janet Kreinbrink" wrote:

Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it to
miliary time (17:00).

Thanks.

I believe it is already in military time, 1700 as far as I know is military
time. 17:00 is 24 hour time used by many countries in Europe and airports
train stations etc, regardless here is the easiest way to convert it if you
want to convert it to real time in 24 hour format


=--TEXT(A1,"00\:00")


then format as hh:mm



--


Regards,


Peo Sjoblom


"Janet Kreinbrink" <Janet wrote in
message ...
Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it
to
miliary time (17:00).

Thanks.

Tried that, but got this message: Microsoft Office Excel cannot use the
number format you typed.

"bob" wrote:

try using this custom frmat: [h]:[m]

"Janet Kreinbrink" wrote:

Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it to
miliary time (17:00).

Thanks.

Figured it out:

=TIME(A1/100,MOD(A1,100),0)

Thanks!

"Janet Kreinbrink" wrote:

Tried that, but got this message: Microsoft Office Excel cannot use the
number format you typed.

"bob" wrote:

try using this custom frmat: [h]:[m]

"Janet Kreinbrink" wrote:

Hi,
I have a large spreadsheet of data from a cvf file. Column B has the time
in this format: 1700. Can't for the life of me figure out how to chage it to
miliary time (17:00).

Thanks.