Importing Mins:Secs

  • Thread starter Thread starter Terry Pinnell
  • Start date Start date
T

Terry Pinnell

I'm getting nicely confused here and would appreciate a little help
please.

For a CD I bought, I had a text file containing details of the
durations of some tracks. It looked like this:
Min Sec
Préambule 02:17
Pierrot 01:58
Arlequin 01:05

I imported it into Excel 2000, where it looked the same.

Adding durations of the first dozen or so tracks gave the correct
answer. For example:

Préambule 02:17
Pierrot 01:58
Arlequin 01:05
Total 05:20

But later totals were clearly wrong. I eventually realised it was
because Excel had assumed, (for reasons I'd be interested to know),
that these were Hours:Mins, instead of Mins:Secs.

So how can I do the following please:

1) Fix this now, after the event. So far, fiddling with changing the
format to Custom mm:ss didn't work.

2) Ensure that future similar imports are correct.
 
What you need to do is divide all the entries you have so far by 60. An
easy way to do this in one go is to enter 60 into a blank cell
somewhere, select that cell and click <copy>. Then highlight all the
cells which you want to change (eg B2:B50) and Edit | Paste Special |
Divide (in the Operations area - check this) then OK and <Esc>.

You will need to format the cells using Custom as [m]:ss

Hope this helps.

Pete
 
Pete_UK said:
What you need to do is divide all the entries you have so far by 60. An
easy way to do this in one go is to enter 60 into a blank cell
somewhere, select that cell and click <copy>. Then highlight all the
cells which you want to change (eg B2:B50) and Edit | Paste Special |
Divide (in the Operations area - check this) then OK and <Esc>.

You will need to format the cells using Custom as [m]:ss

Hope this helps.

Pete

Terry said:
I'm getting nicely confused here and would appreciate a little help
please.

For a CD I bought, I had a text file containing details of the
durations of some tracks. It looked like this:
Min Sec
Préambule 02:17
Pierrot 01:58
Arlequin 01:05

I imported it into Excel 2000, where it looked the same.

Adding durations of the first dozen or so tracks gave the correct
answer. For example:

Préambule 02:17
Pierrot 01:58
Arlequin 01:05
Total 05:20

But later totals were clearly wrong. I eventually realised it was
because Excel had assumed, (for reasons I'd be interested to know),
that these were Hours:Mins, instead of Mins:Secs.

So how can I do the following please:

1) Fix this now, after the event. So far, fiddling with changing the
format to Custom mm:ss didn't work.

2) Ensure that future similar imports are correct.
Click to expand...
Click to expand...

Thanks. But meanwhile I tried an alternative approach that worked OK.
I realised that Excel was expecting HH:MM:SS, and I'd given it only
XX:YY, which it took as HH:MM. So I first used a simple RegEx in my
text editor, TextPad, to prefix all my data with '00:'. Then all was
well.
 
I'm glad you got it sorted out - thanks for the feedback.

Pete

Terry said:
Pete_UK said:
What you need to do is divide all the entries you have so far by 60. An
easy way to do this in one go is to enter 60 into a blank cell
somewhere, select that cell and click <copy>. Then highlight all the
cells which you want to change (eg B2:B50) and Edit | Paste Special |
Divide (in the Operations area - check this) then OK and <Esc>.

You will need to format the cells using Custom as [m]:ss

Hope this helps.

Pete

Terry said:
I'm getting nicely confused here and would appreciate a little help
please.

For a CD I bought, I had a text file containing details of the
durations of some tracks. It looked like this:
Min Sec
Préambule 02:17
Pierrot 01:58
Arlequin 01:05

I imported it into Excel 2000, where it looked the same.

Adding durations of the first dozen or so tracks gave the correct
answer. For example:

Préambule 02:17
Pierrot 01:58
Arlequin 01:05
Total 05:20

But later totals were clearly wrong. I eventually realised it was
because Excel had assumed, (for reasons I'd be interested to know),
that these were Hours:Mins, instead of Mins:Secs.

So how can I do the following please:

1) Fix this now, after the event. So far, fiddling with changing the
format to Custom mm:ss didn't work.

2) Ensure that future similar imports are correct.
Click to expand...
Click to expand...

Thanks. But meanwhile I tried an alternative approach that worked OK.
I realised that Excel was expecting HH:MM:SS, and I'd given it only
XX:YY, which it took as HH:MM. So I first used a simple RegEx in my
text editor, TextPad, to prefix all my data with '00:'. Then all was
well.
Click to expand...
 
Back
Top