Format dates from cvf file

  • Thread starter Thread starter Janet Kreinbrink
  • Start date Start date
J

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.
 
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
 
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
 
Tried that, but got this message: Microsoft Office Excel cannot use the
number format you typed.

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

Janet Kreinbrink said:
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.
Click to expand...
Click to expand...
 
Figured it out:

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

Thanks!

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

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

Janet Kreinbrink said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top