Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Format dates from cvf file
7 views
Skip to first unread message
Kreinbrink@discussions.microsoft.com Janet Kreinbrink
Nov 6, 2009, 2:16:01 PM11/6/09
to
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).
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
Nov 6, 2009, 2:30:53 PM11/6/09
to
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
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 Krein...@discussions.microsoft.com> wrote in
message news:25ED0495-749B-4465...@microsoft.com...
bob
Nov 6, 2009, 5:16:04 PM11/6/09
to
try using this custom frmat: [h]:[m]
Peo Sjoblom
Nov 6, 2009, 5:32:30 PM11/6/09
to
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
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 Krein...@discussions.microsoft.com> wrote in
message news:25ED0495-749B-4465...@microsoft.com...
Janet Kreinbrink
Nov 9, 2009, 3:12:01 PM11/9/09
to
Tried that, but got this message: Microsoft Office Excel cannot use the
number format you typed.
number format you typed.
Janet Kreinbrink
Nov 9, 2009, 4:29:02 PM11/9/09
to
Figured it out:
=TIME(A1/100,MOD(A1,100),0)
Thanks!
0 new messages