Function forumula to treat literal text, 1310.500, as a 'Time Data Type', to permit arithmetic?

English
#1

Cell A1 has the value 1305.500 stored in it. I’d like to do arithmetic on it.
I want to subtract 10 seconds from it, to get the value 1255.500 instead of 1295.500.
I don’t want to change its format (right click on cell>Format cells>Numbers tab), as this makes data entry of times more tedious, and doesn’t permit me to rely only on the num pad of standard keyboard.
I’d like to use cell C1, designate A1’s value as a ‘time data type’ then go ahead and subtract 10 seconds from it.

The following works in Excel, but not in LibreCalc, 0450.000 is displayed:
=REPLACE(TEXT(--REPLACE(A1,3,0,":")-TIME(0,0,10),"mm:ss.000"),3,1,)

How to input times as HHMM without colons
#3

Instead of --REPLACE(A1;3;0;":"), which depends on automatic conversion of the text to time (and which may be also enabled in Options → Calc → Formula → Detailed calculation settings → Details, select “Convert also locale dependent”), use TIMEVALUE(REPLACE(A1;3;0;":")).

#5

‘Convert also locale dependent’ was already enabled.
I’m getting the same output of 0450.000 with:
=REPLACE(TEXT(TIMEVALUE(REPLACE(A1,3,0,":"))-TIME(0,0,10),"mm:ss.000"),3,1,)

#2 
=TIMEVALUE(REGEX(A1;"\d\d\.";":$0"))-TIME(0;0;10)

or more generalized:

=TIMEVALUE(REGEX(A1;"(\d?\d)?(\d\d.)";":$1:$2"))-TIME(0;0;10)

1 Like
#4

This gave an ‘Err:502’ output.

#6

the striked works only for 5 or more digits left of the decimalpoint!

#7

I found the issue. The problem was the number is 3 digits long, instead of 4 digits. E.g. 305
LibreCalc will automatically discard the leading 0, if I type in 0305, instead of just 305.

#8

Well, then you did not enter 1310.500 as literal text. Your cell value is a floating point number and should be treated as such.
decimal_times.ods (17.9 KB)

1 Like
#9

Thank you Villeroy, for the attachment. It was very helpful.

I’d like to mention I’ve gone back and tried to re-apply the original answer’s formula step by step, and now its giving Err:502, I’ve attached the spreadsheet with a screenshow in it.

=REGEX(A2,"\d\d\.",":$0") displays 1305 as intended, but then:
=TIMEVALUE(L2) gives Err:502.
This wasn’t the case in my second response at 3:13pm.

inconsistent.xlsx (47.0 KB)

#10

1305 is not intended, intended is 13:05 instead, but the regex pattern explicitly looks for a . decimal separator, which in the integer number 1305 does not occur. You could circumvent that by forcing fractionals (and integer digits) like in

=REGEX(TEXT(A2;"0000.000");"\d\d\.";":$0")

which here gives 13:05.000 and also works with less than 4 integer digits, like 4 → 00:04.000

Note that anything using TEXT() with decimal separator depends on the current locale and will fail with locales that use a different decimal separator.

3 Likes
#11

You may use MID(1/2;2;1) to get the current decimal separator.
The suggested formula would get a little bit more complicated to work in any locale:

`=REGEX(TEXT(A2;"0000" & MID(1/2;2;1) & "000");"\d\d\.";":$0")`

Anyway: How much time will we waste again and again to support undelimited numbers misused as stubborn and gravely misleading “time values”?
Did I miss the statement by the OriginalQuestioner how to handle (e.g) 21310.500 ? Is the 213 then to interpret as a number of minutes? Shall it mean 2 hours and something ?
It’s a mess.

1 Like
#12

I seized the opportunity of this thread and also of this one, and took the time to develop a serious solution for a nonsensical task.
I felt encouraged to do so because that solution based on user code would contain some routines reusable in serious cases.
It may also be taken as an example backing my thesis that “recognition” of numeric types, related auto-formatting, and exchange of unit-bound measures as numbers or “special numbers” (in csv e.g.) should be reconsidered fundamentally.
The attached spreadsjeet file is not tidied up, and contains a few demonstrations that may require careful consideration for a full understanding.

disask107019_BadTimeHandlingByGoodCode.ods (35.0 KB)

1 Like
#13

Indeed, I keep forgetting about that nifty decimal separator detection…
With the format code 00\:00.000 that brutally forces a time string (see How to input times as HHMM without colons - #9 by erAck) the REGEX() call is also superfluous, so this converts such “number” to an actual time value:

=TIMEVALUE(TEXT(A2;"00\:00" & MID(1/2;2;1) & "000"))

and format the result with [MM]:SS.000

#14

decimal_times2.ods (22.9 KB)

#15

Well yes, TIMEVALUE() returns the fractional part 0 ≤ value < 1, see TIMEVALUE() accepts input which requires "carry minutes" and "carry hours". Intentional? - #2 by EarnestAl. OP didn’t ask for > 24 hours input (in minutes).

#16

Where the result already suppresses multiples of 1440 min.

  1. @risedu: Do not misuse ordinary numbers ignoring their ordinary semantics.
  2. The automatic formatting of values supposed to mean “time” always uses TimeOfDay notation and suppresses therefore multiples of 24 h (=1440 min).
  3. Using results gotten this way in subsequent calculations may lead to serious erros which are hard to fix - and even harder to detect in advance.
  4. Spreadsheets should support reasonable duration formats.
  5. “Mixed-unit-formatting” is next to never used in engineering or science. If used at all, a non-decimal subdivision is especially problematic.
  6. @erAck You might have some fun with the attached example, in which I applied a concept I had designed for duration values and for angle values (1st sheet) to “old British” currency subdivision (2nd sheet once made for @robleyd just for fun). Note: Not every detail has been carefully checked.
    7., The serious background to the example is my conviction that values must be communicated in text formats that contain the unit(s) used. No more “Mars Climate Orbiter” losses.
#17

and in row 3:

12345,333	01:23:45,333	02:03:45,333
#18

Naturally. 12345.333 formatted with 00:00.000 gives 123:45.333, which is 123 minutes and 45.333 seconds.

#19

Oh, was it intended to be without hours? That’s very easy to fix in numeric context.
decimal_times3.ods (21.5 KB)

#20

The OP didn’t specify what they wanted there, whether it’s always minutes_seconds, or for 5 and more digits hours_minutes_seconds.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.