Format dates from cvf file

[Janet Kreinbrink]

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.

 

[Bernard Liengme]

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

 

[bob]

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

 

[Peo Sjoblom]

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]

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

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

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.

 

[Janet Kreinbrink]

Figured it out:

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

Thanks!

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

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

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.