[Calc] Calculating time elapsed problem

[alphaniner]

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.

[erAck]

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.

[alphaniner]

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.

[erAck]

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.

[alphaniner]

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.

[erAck]

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.