[Calc] Stop converting 1:23 to 00:01:23 AM

This is ridiculous. I’m trying to paste song info from itunes. It’s like this

The xx
View In iTunes

When I paste it, it changes it into one row, but thoughtfully automatically converts the length into a time.
1 Angels The xx 02:51:00 AM $1.29 View In iTunes

I can paste special and it will stop the conversion but also stops it from being one row. And sometimes it decides to convert it to money, or a decimal…

HOW DO YOU TURN THIS CRAP OFF! Why would someone include this and not include an off switch? I already tried turning every stupid auto thing off…

If anyone is having this problem, go to google sheets. It just works…

I just tried it on LO on Win 7 Pro 64 bit. I copied your line above and pasted it into Calc using simple paste, paste special -unformatted text, paste special-html, paste only - each of the 3 options. Except for the paste only options - which did nothing, each paste operation gave exactly the line you show above, as it is above, and all in one cell. Not sure what’s different.
Is it possible for you to use 2’51" notation? That pastes in as I display it here.

Tried again for you, copying just 2:51, since I thought your source might have a tab separation that causes the values to go into different cells. As initially pasted, I also get 2:51AM. However, under Format>Cells>Time there are couple of possible options that LOOK like they should work. The [HH]:MM:SS format displays 02:51:00. Removing the [HH] gives :51:00. The MM:SS.00 gives 51:00.00. Editing the format to MM:SS gives 51:00. There ought be something in there that would work!

I just noticed that most of the formats have 02:51:00AM underlying them, as shown in the data entry box. The exception seems to be [HH]:MM:SS.00 which has 02:51:00.00 underlying it.


This “crap” originates from two perfectly cooperating sources. The first one is a permanent force driving spreadsheets into “pretending being smart”. In this case that is: Most users who enter something like “2:51” into a spreadsheet cell actually mean a “time of day” and want to be offered a “fast method”. Rather exceptionally in this case spreadsheets follow an ISO standard: The abbreviated time format with only one colon will evaluate accomplished by “:00” for “zero seconds”. That’s reasonable regarding everyday needs.

The other source is the general tendency of people feeling better with ignoring standards than with regarding them, and, wherever possible, choosing the non-stamdard variant most likely to cause errors, misunderstandings, and even damage.

Unavoidably we (the states, the economy) spend many million dollars every year developing, improving, negociating, … updating standards, and, of course, there are clever people engaged with it. The work done, nearly everybody will ignore the results. There is no international or national (afaik) standard permitting to use the colon for a notation of duration (lenght of time). The correct format for iTunes to tell what they should have told is “2 min 51 s”.

Nothing ridiculous about this. Just ask iTunes to obey standards an start doing it yourself.

I missed something. Actually ridiculous might be the “AM”. I much like the hour from 12:30 PM till 1:30 PM. And: Would you be so kind to tell me how “google sheets” just works? I surely won’t sign in.

Perhaps pasting as unformatted text.

image description