Ask Your Question

How to convert hh:mm:sec to DEC.?

asked 2017-02-28 13:38:36 +0200

abi gravatar image -> decimal ... Using '=(val)/60 ... one problem; it does not Work!



edit retag flag offensive close merge delete


Time you will find is both simple and complex at the same time. There are many different representations for time and date, and many more useful functions for working with times and dates. Much too much material to be covered here without further confusing you. Rather, allow me to suggest you find the documentation on these and study it. I promise you will be rewarded, in short time. -- cheers

EasyTrieve gravatar imageEasyTrieve ( 2017-03-01 01:18:29 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-02-28 13:54:37 +0200

Lupp gravatar image

updated 2017-02-28 14:05:32 +0200

Your =(val)/60 isn't a formula at all. You need to be much more precise about what you want to achieve.

Just an info you may have missed:
Time-Of-Day values (if correctly created) are numerical values in the range from 0 (included) through 1 (excluded). By that they give the fraction of a day already elapsed. With different words: Calc (and spreadsheets in general) use the day as their implicit unit of time. (This has issues, of course, as everything as close to everyday life.)

If you tell us, you want to convert a time value to decimal, this contains a misunderstanding. You can display the time in a decimal format by simply changing the 'Numbers' format. If you want to get the numeric value using a different unit, you have to apply a conversion factor. To get 12 using h (hours) as the unit instead of 0.5 using d (days) e.g. you obviously need to multiply by 24. (24 h = 1d)

If your "Time" as present currently in your sheet is actually text in the hh:mm:ss format (or a reduced version starting with the hh part) you can apply the TIMEVALUE function first.
If the current representation of the time consists of three separate parts lik the hh part in A1, mm part in B1 and ss part in C1 you get the time by =TIME(A1; B1; C1). Next comes the conversion factor as already mentioned.

Please note: Using different units for any kind of quantity in a spreadsheet requires some understanding of the underlying principles. Otherwise it is very likely to cause errors.

(Durations may exceed 24 h, of course. They are generally not handled correctly, however.)

edit flag offensive delete link more

answered 2017-02-28 13:44:23 +0200

updated 2017-02-28 13:46:59 +0200

Decimal what? minutes? nanoseconds?

Time in LO is actually a fraction of a day that is shown in time format. E.g., 12:00 pm is 0.5. You may use something like this: =SECOND(A1)/60+MINUTE(A1)+HOUR(A1)*60

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-28 13:38:36 +0200

Seen: 5,709 times

Last updated: Feb 28 '17