Ask Your Question

[Calc] Calculating time elapsed problem [closed]

asked 2018-05-16 16:48:24 +0200

alphaniner gravatar image

I have this formula for calculating time elapsed:

=INT(G3-G2)&" days, "&HOUR(G3-G2)&" hours, "&MINUTE(G3-G2)&" min, "&SECOND(G3-G2)&" sec"

In Excel, I can paste a time such as "May 15 13:41:58" and it is automagically converted to "5/15/2018 20:30"* which is usable by the formula. In Calc, the conversion doesn't occur so the formula fails to produce a result. I'm not savvy enough with Calc or spreadsheets in general to know how to resolve this, so any input would be appreciated.

* That's what shows up in the cell; the formula box actually shows "5/15/2018 8:30:25 PM" when the cell is selected.

As a side note, I'd actually like any days to be automatically converted to hours and added to the hours total if anyone knows how that can be done.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-05-17 20:54:14.457208

1 Answer

Sort by » oldest newest most voted

answered 2018-05-16 17:51:24 +0200

erAck gravatar image

Maybe Excel assumes the current year when pasting a long date string that lacks the year, Calc apparently doesn't. May 15, 2018 13:41:58 yields the datetime value 05/15/2018 13:41:58

To subtract two datetime values and get a result in hours,minutes,seconds simply use =G3-G2 and format it as [HH]:MM:SS or [H]" hours, "M" minutes, "S" seconds" if you wish.

edit flag offensive delete link more


Thanks, that was indeed the issue. Knowing that I was able to use text functions in intermediate fields to shoehorn in the year, which result in a usable value:

=LEFT(G3,FIND(" ",G3,FIND(" ",G3)+1)-1)&" "&YEAR(TODAY())&" "&RIGHT(G3,LEN(G3)-FIND(" ",G3,FIND(" ",G3)+1))

Yeah, I know it's an incomprehensible mess, but I couldn't figure out how to do it with regex so... -_-

And I ended up using DAYS() and a bunch of math to simplify the elapsed time result to hours & minutes.

Thanks again.

alphaniner gravatar imagealphaniner ( 2018-05-16 21:08:20 +0200 )edit

Does that format Monthname DD HH:MM:SS have widespread use for input? Calc does accept May 15 for 05/15/2018 so maybe could be taught to accept the combined datetime form as well.

erAck gravatar imageerAck ( 2018-05-17 13:12:49 +0200 )edit

It's a timestamp in the log of a network appliance from a small company.

I suppose if there was much demand for the behaviour it would already be implemented. Anyway, I'm content with the workaround. The calculation is only for convenience anyway.

alphaniner gravatar imagealphaniner ( 2018-05-17 15:37:15 +0200 )edit

I'd submit a bug report against that "appliance", such locale dependent timestamp format is unsuitable for many purposes, it doesn't even sort correctly in a simple text based sort. An ISO 8601 based format like 2018-05-15 13:41:58 would be suitable.

erAck gravatar imageerAck ( 2018-05-17 20:53:31 +0200 )edit

Question Tools

1 follower


Asked: 2018-05-16 16:48:24 +0200

Seen: 137 times

Last updated: May 16 '18