Time based question

  • Thread starter Thread starter BP
  • Start date Start date
B

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.
 
BP said:
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 ##.###
Click to expand...
....

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.
 
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


Harlan Grove said:
BP said:
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 ##.###
Click to expand...
...

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.
Click to expand...
 
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.
Click to expand...

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


--ron
 
I have found the solution.

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

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

Thnx
Click to expand...

Something like:

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

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