Reformatting numbers

**Huwbob** · 04-06-2009, 10:16 AM

I have some data which is exported into excel by a package I can't amend. The information comes into excel in the format

00:00:04 (representing hh:mm:ss) but if I look at the formatting of the cell it says the formatting is general. I want to be able to cut and paste this into another spreadsheet and then apply conditional formatting to colour the text if the value is above 00:00:09, but try as I might, I can't! When I cut and paste (even using paste special) it still retains its previous format, and I still can't amend it

Any suggestions?

TIA

**NBVC** · 04-06-2009, 10:19 AM

Perhaps you can change the formatting to time.. by selecting the column of times and going to Data|Text to Columns and simply clicking Finish....

**Andy Pope** · 04-06-2009, 10:20 AM

can you post workbook with some examples of the values that do not work.

**Huwbob** · 04-06-2009, 10:52 AM

Here you go, I've pasted the data into the are headed "average call length"

**NBVC** · 04-06-2009, 10:59 AM

So like I said, just select the range B58:B72 and go to Data|Text to Columns and click Finish... this converts the selection to proper times... and your Average works out.

You can change the formatting from h:mm:ss to hh:mm:ss if you wish through Format|Cells >> Number Tab

**martindwilson** · 04-06-2009, 11:03 AM

as nbvc said once pasted use text to columns as directed this will reformat as time
then use cf cell value is greater than 0.000104166666666667 or there abouts lol