I have a cell with hh:mm:ss formatted values of say 2:02:48, but need to run some calculations. I want to convert this to the just the number of seconds … in this case 7368 seconds, but can’t see how to do it.
The seconds should be like below
2:02:48 - 7368
00:00:37 - 37
I am confused on the calculation to convert the time to seconds in the libreoffice.
Use =A1*86400 if the time is in A1.
Time values are treated as the numerical ratio of the time under discusson and one full day (24*60*60 s) by spreadsheets. With different words: The implicit unit used is 1 d.
Thus 1d = 24 h = 24* 60 min = 24 * 60 * 60 s = 86400 s
Your examples (slightly enhanced the second one):
2:02:48 = 0.0852777777777778 numerically in Calc meaning 2 h 2 min 48 s = 7368 s
00:00:37.534 = 0.000434421296296296 numerically meaning 37.534 s
Please note that time values handled under the standards of Calc (in days) cannot be added or otherwise put together with the non-standard time values expressed in seconds without a conversion by *86400 or /86400 respectively.
Edit (regarding the remarks by the OQ in his self-answer):
The numerical result for =(A1-A2)*86400 you get is surely the correct number of seconds for the duration. It is just badly formatted (probably by one of the many silly smart automatisms, probably by yourself unintentionally). I got the result shown correctly using the same formula (in a virgin cell).
Being an integer this result formatted to time (TOD) format will show 00:00:00 as any other integer also would. Remember my explanations about the handling of date-time in spreadsheets. The time part of date-time values always is coded in the fractional part of the numerical value which obviously is 0 for the correct result of 1642 as it is for any integer. If Calc badly formatted the cell, just reformat it to your preferred variant from the ‘Number’ list, and you will get shown 1642 or something equivalent.
By the way: The date-time values you posted here are in an unusual format DD-MM-YYYY HH:MM:SS I would advise against. I know that there are many regional preferrences (mostly very doubtable), but the dash-delimited date format you use might not be due to “locale affairs”. Why not use the well designed ISO 8601 format YYYY-MM-DD?
Hi Lupp,
Thanks for your prompt response. But this is not working for me.
14-04-2016 10:51:22(A1) - 14-04-2016 10:24:00(A2) = 0.01900463
(A1 - A2)*86400 = 1642
This is working in your point of view in excel. I have tried this before.
The same is showing 00:00:00 in libre office.
14-04-2016 10:51:22(A1) - 14-04-2016 10:24:00(A2) = 0.01900463
(A1 - A2)*86400 = 00:00:00
Please suggest me on this.