Calculating minutes and seconds

[AllesMeins]

Hi,

maybe this is a stupid question. I just want to enter minutes and seconds in one column. Every time I enter something like 2:54 it gets converted to 02:54:00 meaning hours and minutes. Is it possible to change this behavior, so that OOo converts the input to minutes and seconds (without having to type 0:02:54)? Changing the format of a cell to MM:SS seems only to affect the output (meaning 2:54 is still converted to 02:54:00 and only the minutes and seconds are displayed).

[Villeroy]

I don't think you can not overcome this annoyance without introducing one or two helper columns.
Option 1: Just type your MM:SS and ignore the resulting HH:MM:SS. Divide your input values by 60 and format as MM:SS.
Option 2: Type minutes and seconds in different columns, calculate the times =Minutes/1440+Seconds/86400 and format as MM:SS.

[Dave]

Are you doing calculations using these entries, or just observing them?

David.

[AllesMeins]

I want to be able to at least add them up correctly.

[TerryE]

Dates and times are store as an integral number of days plus the faction that denotes the fractional days. If you format 0 as a date you'll see the absolute date base. Hence adding 1.25 to a date/time bumps it by 1 day 6 hrs.

So yes you can do sensible arithmetic on date and time fields.

[Dave]

I want to be able to at least add them up correctly.

Then Villeroy answered your question. Both he and I, and others here have been doing this for a few decades. The spreadsheet does some common tasks automatically. Otherwise, you need to learn to program it to your needs. I did some spreadsheets on fractions for use in the classroom. I can't tell you how many columns and manipulations I had to go through to get the results needed. For example , you can set A1, B1, C1 as fraction, formatted -1234 10/81. Now put 7/11 in A1, 2/3 in B1, and find the sum in C1. Not good enough for a question that wants an exact result as a mixed fraction. It was so long ago now, I don't think that the sheets even had the "fraction" option at all back then.

In other words, take the advice. Extract the minutes for one column, the seconds for the other. Make it look as you want appearance-wise with a colon in another intermediate column, hiding the initial column for printing, perhaps. Do the arithmetic, adjusting as necessary. All spreadsheets need programming to one degree or another, sometimes to the level of higher-level language programming requiring a course of study [but this one isn't that bad.]

Good luck.

David.

[TerryE]

The challenge is that there is a helper routine within the cell input system which does smart formatting of numeric fields. As I stated in my last post, dates are just treated as a numeric field. When the input text is a valid date format for example 1/3, 1 jan, 1.1.08 then the value is converted to a date and a default date format is applied (in my case DD/MM/YYY). If it is a valid time like wise, with a default time format HH:MM:SS (again internationalisation specific).

• So 1:12:00 becomes 01:12:00
  1:12 becomes 01:12:00, but
  1:12.0 becomes 00:01:12, yet oddly
  1:12. becomes 01:12:00

In the current version of Calc there is nothing you can do that I know of to override the smart formatting defaults. Maybe Villeroy and Dave know better. So the preferred option is to enter the minutes and seconds in separate columns as they suggest. A somewhat tacky botch alternative is to use the custom format [H]:MM. The [H] designator is an undocumented way of entering hours without the modulus 24 carry into days for display. If you do this then 1:20 will display 01:20 and 30:0 will display 30:00 but you would need to divide this value by 60 to use it in a time calc since you are really displaying HH:MM and not MM:SS. Also there are pitfalls: entering 1 will result in 24:00 (remember my previous post). As I said a botch.

[Dave]

Maybe Villeroy and Dave know better.

Villeroy, perhaps. I just try the simpler ones, and offer a few suggestions to hopefully point in the right direction.

David.