Excel data in text, wont update to time format

[Guest]

Hello, i have a problem with excel field formatting. The data is in text
format, but i need it to be in time format. When i select a field and select
time as formatting then it doesnt updatete the field and i still cant sum the
fields. It only updates the field when i select a field, put the cursor at
the end of the data and press enter.Then it sums, but when there are millions
of fields this isnt rational. How to make excel updatate field formatting.
Thank You

 

[oldchippy]

Hello, i have a problem with excel field formatting. The data is in
text
format, but i need it to be in time format. When i select a field and
select
time as formatting then it doesnt updatete the field and i still cant
sum the
fields. It only updates the field when i select a field, put the cursor
at
the end of the data and press enter.Then it sums, but when there are
millions
of fields this isnt rational. How to make excel updatate field
formatting.
Thank You

Hi raigo,

When you say millions of fields are they in the same column, if so

insert a new coulumn and insert

=A1*1

then copy and paste values into the original cells, then format to
time

oldchippy

 

[Guest]

oldchippy's suggestion is correct in that it will force Excel to convert the
text to the numeric equivalents. You can do this without a helper column,
however. Enter a 0 in some random cell and Edit > Copy (or just ctrl+c).
Then highlight the cells you need converted and Edit > Paste Special, select
Values and Multiply, then click OK.

 

[Guest]

Sorry; select Values and Add.

oldchippy's suggestion is correct in that it will force Excel to convert the
text to the numeric equivalents. You can do this without a helper column,
however. Enter a 0 in some random cell and Edit > Copy (or just ctrl+c).
Then highlight the cells you need converted and Edit > Paste Special, select
Values and Multiply, then click OK.

 

[oldchippy]

oldchippy's suggestion is correct in that it will force Excel to convert
the
text to the numeric equivalents. You can do this without a helper
column,
however. Enter a 0 in some random cell and Edit > Copy (or just
ctrl+c).
Then highlight the cells you need converted and Edit > Paste Special,
select
Values and Multiply, then click OK.

Hi bpeltzer,

Don't you mean put 1 into an empty cell, only if you multiply by 0 it
doesn't work

oldchippy

 

[Guest]

Thanks for your replies. Why is it so that excel doesnt allow just to select
a satisfying format from "format cells" in that case? If i copy it i am
getting in trouble with formatting as the file consists of merged cells and
the pasted page has to be in same forming. And data is not in one column,
there are lots and lots of them