Excel data in text, wont update to time format

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

Originally Posted by raigo

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

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.

"raigo" wrote:

> 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

Originally Posted by bpeltzer

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.

"raigo" wrote:

> 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 bpeltzer,

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

oldchippy

Sorry; select Values and Add.

"bpeltzer" wrote:

> 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.
>
> "raigo" wrote:
>
> > 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

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