TIME (hour, minute, second)

Description:

This function returns the decimal number for a particular time. The total time for the day is considered as equal to 1 and the time that is calculated from the function represents what fraction of 1 is covered until then.

Syntax: TIME (hour, minute, second)

• Hour : is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the; remainder will be treated as the hour value. For example, TIME (27, 0, 0) = TIME (3, 0, 0) = .125 or 3:00 AM.
• Minute : is a number from 0 to 32767 representing the minute. Any value which is greater than 59 will be converted to hours and minutes. For example, TIME (0,750, 0) = TIME (12, 30, 0) = .520833 or 12:30 PM.
• Second : is a number from 0 to 32767 representing seconds. Any value greater than 59 will be converted to hours, minutes, and seconds.  For example, TIME (0, 0, 2000) = TIME (0, 33, 22) = .023148 or 12:33:20 AM.

Remarks

• All the arguments are compulsory.
• Time values are a portion of a date value (SERIAL NUMBER) and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

The TIME function can be used in any database where you have stored values for the hours, minutes and seconds, and if you want to return the time, as a computation of all these parameters, in this case it is possible to access this value with the help of this function. This function displays the time as a number that is a computation of all the parameters supplied to this function.

=TIME (21, 45, 06)	-The parameters for Hour, Minutes and Seconds, are converted to the time returned by this   function which is a number converted into decimal value. (The time returned for this function is = 0.91)
=TIME ("range reference")	-The parameters for Hour , Minutes and Seconds can be specified from a given range instead of being fed into the formula directly and the TIME can be calculated based on the values thus passed to the formula.

 

Let us take the example of the HR database in PowerOLAP where we require to calculate the value for time, based on the information provided by the "hour", "minute" and the "second" column. In this case, we have written a formula in the appropriate cube in PowerOLAP, pulling in values from the "hour", "minute" and the "second" members, and then we have written those values to this formula. The formula then calculates the "Time" as a decimal number which is a fraction of 1 and which derives its values based on the parameters that are given to it as an input, and these are pulled in from the other members in the same dimension.



The formula above writes values into the "Staff Date Lookup" cube to the "Staff Date Lookup" dimension into the member named "TIME" by calculating the appropriate value from the "Hour (Hr)", "Minute (Min)" and Second (Sec)" members which are the parameters supplied and which help the function to determine the "Time".