Time based question

[BP]

I am importing times from another application (that exports data as a csv
file.)

When opening in Excel, the first number is dropped and converts the time to
a number.

For example.a time of 52.350 seconds should be seen as ##:##.### which is
seen in Excel as 12:00.534 AM

But excel only see's ##.###

If you modify the cell by inserting a "0:" before the number then Excel
correctly reads it as a time.

Is there anyway by formula to insert the "0:" before each number. I have
over 6000 cells to change and reluctant to do this manually.

Many thanks,
Brett.

 

[Harlan Grove]

I am importing times from another application (that exports data as a csv
file.)

When opening in Excel, the first number is dropped and converts the time to
a number.

For example.a time of 52.350 seconds should be seen as ##:##.### which is
seen in Excel as 12:00.534 AM

But excel only see's ##.###

....

Presumably Excel is importing your 52.350 as the number 52.350. If so, and
if all these values are seconds including fractional seconds, then import
them as-is, then enter 86400 in some blank cell, Edit > Copy that cell, then
select the range containing these seconds values, and run Edit > Paste
Special, select [Paste] Value and [Operation] Divide in the Paste Special
dialog, and click OK. Then give this range the number format ss.00.

 

[BP]

Thats brilliant. It worked fine.
One final question.

In my list of times, 6000 of them, there is a combination of those under and
over 1 minute, so I cannot easily apply the formulae to all cells.

Do you know how to write an if statement that says:
If the cell is a number then x, otherwise y.

What I want to do isolate those cells I need to change and leave the correct
time based cells as is.

Many, many thanks.
Brett

I am importing times from another application (that exports data as a csv
file.)

When opening in Excel, the first number is dropped and converts the time to
a number.

For example.a time of 52.350 seconds should be seen as ##:##.### which is
seen in Excel as 12:00.534 AM

But excel only see's ##.###

...

Presumably Excel is importing your 52.350 as the number 52.350. If so, and
if all these values are seconds including fractional seconds, then import
them as-is, then enter 86400 in some blank cell, Edit > Copy that cell, then
select the range containing these seconds values, and run Edit > Paste
Special, select [Paste] Value and [Operation] Divide in the Paste Special
dialog, and click OK. Then give this range the number format ss.00.

 

[Ron Rosenfeld]

Thats brilliant. It worked fine.
One final question.

In my list of times, 6000 of them, there is a combination of those under and
over 1 minute, so I cannot easily apply the formulae to all cells.

Do you know how to write an if statement that says:
If the cell is a number then x, otherwise y.

What I want to do isolate those cells I need to change and leave the correct
time based cells as is.

What do the cells that are => 1 minute look like?


--ron

 

[BP]

Numbers below 1 minute look like 52.345
Above 1 minute they look like 01:01.693

Thnx

 

[BP]

I have found the solution.

If the value is greater or less than 1 is the determining factor.

Thanks.

 

[Ron Rosenfeld]

Numbers below 1 minute look like 52.345
Above 1 minute they look like 01:01.693

Thnx

Something like:

=IF(A1<1,A1,A1/86400)

and format the cell as [mm]:ss.000
--ron