Gnumeric formatting problem.

jinpa

I could find workarounds for this, but I'm curious if there's any way of handling this problem within Gnumeric.

I have a list of paces, e.g.

15:16
15:48
16:16
16:05
17:36
16:18
17:30
17:50
15:51

I want Gnumeric to recognize them as minutes and seconds. However, Gnumeric thinks they're hours and minutes. And so when I change the format to minutes and seconds, Gnumeric thinks that they were already hours and minutes, and so converts that to minutes and seconds.

For example, suppose I had a time 10:02. (ten minutes and two seconds)
Then I formatted it as minutes and seconds. It will change it to 602:00.

Even if I have the cells already formatted as minutes and seconds, as soon as I put a formula in there, it messes it up somehow and changes the format to hours and minutes (but gets the math wrong) and then when I change the formatting to minutes and seconds, it assumes it's already hours and minutes.

So the problem is that it's trying to help me too much. I want to change the format without it second-guessing me.

There doesn't seem to be a Gnumeric forum in existence.

P.S. So the problem seems to happen when I try to multiply an integer (miles) times a pace in mm:ss. So I have a formula

=(F1194+I1193/2)*H1193

Where F and I are integers, and H is a pace in the format mm:ss.

Tsaukpaetra

@jinpa said in Gnumeric formatting problem.:

Even if I have the cells already formatted as minutes and seconds

Well Shit, that was going to be my suggestion.

I suppose the alternative would be to have the column as text, split it, reconstitute the values in formula, and then format the result.

Benjamin Hall

Does prepending 00: help? Basically make it look like HH:MM:SS to convince it that it's that format?

aitap

@jinpa said in Gnumeric formatting problem.:

I want Gnumeric to recognize them as minutes and seconds. However, Gnumeric thinks they're hours and minutes. And so when I change the format to minutes and seconds, Gnumeric thinks that they were already hours and minutes, and so converts that to minutes and seconds.

That sounds infuriating. I think there's no way in Gnumeric to reinterpret the same textual representation according to a different format. I hoped I could trick it by changing the format to text, then to mm:ss, but the first step already transformed 1:23 into 0.0576.

Even if I have the cells already formatted as minutes and seconds, as soon as I put a formula in there, it messes it up somehow and changes the format to hours and minutes (but gets the math wrong) and then when I change the formatting to minutes and seconds, it assumes it's already hours and minutes.

Hmm. This doesn't seem to happen for me in Gnumeric 1.12.32. I was able to set the m:ss format on a range of cells, then enter a formula on a time (also in m:ss format) and a bunch of integers. It seems to give the right answers if I manually redo the same calculation in minutes: times.gnumeric

jinpa

ReducedExample.gnumeric

Illustrating the problem. (Comments about pace can be discussed in the lounge.) Cell E3 should be slightly less than 2 hours.

Cell C2 is formatted hours:minutes. But if I change it to minutes:seconds, it recalculates it.

This is Gnumeric 1.12.44.

jinpa

@Benjamin-Hall said in Gnumeric formatting problem.:

Does prepending 00: help? Basically make it look like HH:MM:SS to convince it that it's that format?

I thought about that approach, and if I was on the job, I would probably do it that way to save time. But I figure there must be a right way of doing it within Gnumeric, and it bugged me that I didn't know what it was. I confirmed that LibreOffice Calc has the same behavior. LibreOffice is pretty much designed to be an exact copy of Excel, warts and all. So if it has an odd behavior, it's probable that Excel does too. And I figure that Excel must have a preferred way of dealing with it.

loopback0

@jinpa Excel does it too, and as mentioned already I'd just put 00: at the start to defeat it.

Arantor

Try dividing by 60.

Gnumeric appears to share the brain measles that is 'a date time is expressed as a floating point number such that the integer section represents a number of days since 1900, and the floating point section represents time'.

Since your current set is being interpreted as hh:mm when entered, and converted to a fraction, you should be able to divide what you have by 60 to get from hh:mm to mm:ss since it's 1/60th the size but same format selector.