I want to be able to use a time formatted as time in a calculation in a cell.
For example I want to enter something like: =UnknownTimeToDecimalFunction(25:0:0) * 10
which would result in either 250:0:0 or its decimal equivalent.
Does such a function exist?
Hello,
To be very precise in wording: Your questions starts a bit undefined, since 25:0:0 is not a time (a time is a fraction of a day and has a maximum of 1 which corresponds to 24 hours) but a duration [in hours, minutes and seconds]. There is no need for something like a function “UnknownTimeToDecimalFunction”, since existing time or duration is a decimal number. If not, it is not a time or duration but is a Text looking like a time or duration. What you really have could be determined by setting option View -> Value Highlighting. If it turns to blue, you have numeric values and a time or duration. If it appears in black color it is of type “text”.
Assuming you have really a correct duration in cell A1 and want the result in B1 then:
- Use formula
=A1*10in cellB1 - Format cell
B1using Format Code:[HH]:MM:SS(the square brackets assure the data to be presented as duration and this way allowing for number of hours being greater than24)
(If you have duration as text use something similar to =VALUE("25:0:0")*10 and format as [HH]:MM:SS).
Hope that helps