Can not get data to change when I format the cells

[deja2000]

Monthly I run a report that adds up all the minutes of long distance our client uses on our phone lines. The data is exported to excel and the cells in the "duration" column start off in the General format. To use the sum function to add the total number of minutes and seconds I need the column to be in Custom format, "[h]:mm:ss". When I update the cells in the duration field from General to the Custom format nothing changes. Normally, if say, "0:33:00" was a cell that I've formatted with [h]:mm:ss the formula bar would display "12:30 AM" This shows the cell is reading the data as a time.

But nothing I do to the "duration" column changes the formatting of the data. I have tried:

• Adding VBA to make sure formatting stays when you cut and paste. Then I cut the data from the "duration" column and pasted it in a column that was already formatted.
• Linking the data from the "duration" column to cells that are already formatted.

The only thing I did that correctly formatted the data was to retype the it in a new cell. Unfortunately that will not work because there are over 2000 cells in the "duration" column.

Please help, I have been at this all day!!!

[lucas]

try right clicking and select pastespecial. From the dialog select the value radio button.

[mike31z]

deja2000

Try this, sometimes when you inport data in to excell its seen as text even if its appears as a number.

In and extra column use the trim formula "=trim(a1)" Change A1 to reflect the first cell in the "duration" column. Trim removes all excess text characters. Then format the column with the trim formula to you time needs.


Mike

[vzachin]

deja2000,

did you try to format the column before the data is imported?

zach

[SamT]

What exactly does the data in the Duration column look like in General format?

[deja2000]

try right clicking and select pastespecial. From the dialog select the value radio button.

Lucas,

The data is imported from a web based reporting system. When it imports into MS Excel it is all converted into General format. I then cut and paste that inported data into a MS Excel template in another workbook. But for some reason once it imports MS Excel will not read the numbers as time even when I reformat the cells.

[deja2000]

deja2000

Try this, sometimes when you inport data in to excell its seen as text even if its appears as a number.

In and extra column use the trim formula "=trim(a1)" Change A1 to reflect the first cell in the "duration" column. Trim removes all excess text characters. Then format the column with the trim formula to you time needs.


Mike

Mike,

I tried using the "=trim" formula but I am still unable to get Excel to read the data in the "=trim" column as time. So it the sum formula I am using still doesn't provide the total sum of the column with the "=trim" data.

[deja2000]

What exactly does the data in the Duration column look like in General format?

Sam,

In general format the data in the duration column displays as: [h]:mm:ss or the duration of a 3 second call would show as 0:00:03. But typically when I'm working with time the data in the cell should display as [h]:mm:ss but when I click on that cell in the formula bar it should show as 12:00:03 AM.

[SamT]

The data is imported from a web based reporting system.

Do you mean the WWW/Internet or a company network?
How is it inputted to the web?
In what Format on the web?
What is the raw value of this data

--------------------------------------------

When it imports into MS Excel it is all converted into General format.

How is it / What imports it to Excel?
Imported to which Workbook? What is the Format of that Workbook's Cells? What does the data look like here?

---------------------------------------

I then cut and paste that inported data into a MS Excel template in another workbook.

What is the format of this Template Workbook's Cells?
What does the data look like if you enter a [h]:mm:nn number directly?

-----------------------------------------

But for some reason once it imports MS Excel will not read the numbers as time even when I reformat the cells.

Whoa! Is this the first workbook the data sees on it's way from the web to the Template, or is this the Template Workbook?


==========================


Forget all that.

1)
What is the raw data input to the web?
Is it formatted on the web? To what Format?

2)
What is the raw data imported to the first workbook?
Is it formatted in that workbook? To what Format?

3)
What is the Raw Data Pasted into the Template Workbook?
Is it Formatted in the Template book? To what Format?

Yeah, I know you can't get it to the template, but, what if.

[mike31z]

deja I think that the number you get exported into excel is a text number.
In an extra column enter the foling formula like the TRIM i suggested.
Formula "=VALUE(A3)" the result should be a number like shown in B3
In B4 I used the same formula and formatted the cell to display th h:mm:ss.

I have tried to attach a small xls file with the value formula.

[deja2000]

=========================


Forget all that.

1)
What is the raw data input to the web?
Is it formatted on the web? To what Format?

The report is ran from our ShoreTel VOIP phone server. The server displays the data of the report in an online spreadsheet application using OpenOffice.org. The file it creates is called, "User Activity Rpt Nov 2009.xls-OpenOffice.org.calc" The data in the duration column is Text.

2)
What is the raw data imported to the first workbook?
Is it formatted in that workbook? To what Format?

Because I am running the report on the server I need to get the data from the server to a place on our network. So from the OpenOffice.org file I am able to Save As a MS Excel 97/2000 XP [.xls] file in a network folder. This file creates a new workbook called: "User Activity Rpt Nov 2009.xls". All cells in this new workbook are formated as General

3)
What is the Raw Data Pasted into the Template Workbook?
Is it Formatted in the Template book? To what Format?

The data is still text when it is pasted into the Template Workbook where I need to sum the total of the "Duration" column.


But after going back into the server to get the answers for you I think I may have figured it out. I believe i need to start with the data in the OpenOffice.org file. If I update those cells from text to time then the data should be in the correct format when it is saved into the [.xls] file.

[SamT]

Glad to have helped

You can also try the CDate function

[Aussiebear]

Can you post a workbook with some dummy data, so we can see what it is you are looking at. Just change the clients identifying data, and any cofidential information. THis way we can all see the data as it comes into the workbook.

It'll save all the extra questions being posted.