How to give duration in excel or libre calc?

I have duration data like 5:23, etc... in m:ss format. I already typed the data, but I cannot create a diagram, because none of the mentioned excels understand it. If I change the data type to user defined time with m:ss template, then it converts the data into something else. I tried to create new columns with this data type and copy-paste the data (including special paste etc...), but I got the same result. Is there a solution, or are these software really such a ...?

inf3rno

Posted 2015-07-10T19:07:10.503

Reputation: 931

Diagram = chart? Can you be more specific about exactly what you want to do with the timestamp? Excel charts understand dates/times and will chart them with little trouble. The main issue comes down to whether or not the date/time is stored as a date/time or is stored instead as a string/text. If it is stored as a date then everything works fine. – Byron Wall – 2015-07-10T19:47:46.097

@ByronWall I want to do something like this: http://kepfeltoltes.hu/150710/javul_s_www.kepfeltoltes.hu_.png As you can see the m:ss is imported here as h:mm. That's all I could achieve. (these are running lap times per km) Ohh btw. there is a big difference between timestamp and duration.

– inf3rno – 2015-07-10T20:05:38.563

You can change the number format for the axis to display mm:ss if the value is stored properly as a minute/second value. The default for Excel is to interpret entry as HH:mm so you should enter as 00:mm:ss if you want it to work by default. You can also use TIME() to build the correct time with minutes and seconds entered. Once you have the data entered correctly, the charting should just work. – Byron Wall – 2015-07-10T20:08:19.877

@ByronWall I entered the data correctly. I won't override it, just because excel lacks data import feature. I would rather use plain json with a d3 chart than fixing manually what ms can't do for 10 years. – inf3rno – 2015-07-10T20:12:09.217

Not sure if the MS rant is getting us anywhere... you could do =TIME(0,HOUR(A1),MINUTE(A1)) to "move" the HH:MM over to MM:SS. The upside is that since it is a date formula, you are guaranteed to get a date/time for use in your chart. – Byron Wall – 2015-07-10T20:17:50.653

Answers

I played around a bit LibreOffice Calc and you need time in the format HH:MM:SS.

But you can display it in the format MM:SS.

If you have time already as text M:SS you can convert it to the correct time format at follows.

place text M:SS values in column A with type of text
put this formula in column B =TIMEVALUE(CONCATENATE("0:"; A1)) it will change the M:SS input format into HH:MM:SS
Copy column B, and special paste it over A, no formula just values
Now format column to Time-> MM:SS

And there you have your data in the desired format, and Calc understands it.

Pieter

Posted 2015-07-10T19:07:10.503

Reputation: 138

Calc and Excel need the first number in a time entry to be hours. So if you are entering minutes you have to add a 0: at the front of your duration: 0:5:23 etc. even though you have the cell formatted to not show the hour.

Alternatively you could have one column for minutes (say column A for this example), a second column for seconds (say column B), and use a calculation to diplay the entered time: =A1/1440+B1/86400 where the division converts minutes and seconds respectively into fractions of 1 day (fractions of 1 day is how spreadsheets store time internally). Copy and paste the formula down as far as needed. (Of course the display column needs to be formatted as [mm]:ss or similar to display correctly.)

You could even get fancy and have the display column only show a value if you've entered numbers in the minutes or seconds column: =IF(OR(A1<>"",B1<>""),A1/1440+B1/86400,"") Note that LibreOffice or OpenOffice would need semicolons ; where Excel uses commas ,

Lyrl

Posted 2015-07-10T19:07:10.503

Reputation: 560

Sorry, I cannot accept solutions, which require data format changes. I want to give the durations as m:ss. – inf3rno – 2015-07-10T20:16:02.683

1@Lyrl, Note that there is a TIME function which saves you from having to do math with minutes and seconds per day. – Byron Wall – 2015-07-10T20:19:00.123