# [Calc] Calculating time elapsed problem [closed]

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 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

Sort by » oldest newest most voted

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.

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.

( 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.

( 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.

( 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.

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