Integer times

Excel calculates with times as parts of 24 hours. These decimal values can be used to perform different calculations. Some people want to be able to enter time values as integer numbers, e.g. the integer number 1000 equals the time value 10:00. These integer time values can not be used in calculations without converting them to a serial time that Excel recognizes, or an ordinary decimal value.

The worksheet formula below will convert an integer in cell A1 to a serial time value (1000 => 0.41667) :

=(TRUNC(A1/100)+MOD(A1,100)/60)/24

The worksheet formula below will convert an integer in cell A1 to an ordinary decimal value (1030 => 10.5) :

=TRUNC(A1/100)+MOD(A1,100)/60

The worksheet formula below will convert a serial time value in cell A1 to an integer time value (0.41667 => 1000) :

=TRUNC(A1*24)*100+(A1*24-TRUNC(A1*24))*60

The worksheet formula below will convert an ordinary decimal value in cell A1 to an integer time value (10.5 => 1030) :

=TRUNC(A1)*100+(A1-TRUNC(A1))*60

Document last updated 2000-02-04 12:45:22 Printerfriendly version

User comments:

Ole P. from Norway wrote (2005-08-15 13:37:28 CET):

Re: convert numbers to text with /100 only
You can download an example here.

warren from Israel wrote (2005-08-14 10:42:32 CET):

convert numbers to text with /100 only
any ideas how to convert numbers with text + decimal 100 only

e.g . 100.50 = One Hundred & 50/100 only

Ole P. from Norway wrote (2004-04-18 21:36:45 CET):

Convert a decimal value to an Excel time value
You can convert e.g. 8.5 to 8:30 like this:
8.5 / 24 = 0.3542
If you apply a time value number format (e.g. h:mm) to the result cell, it will display 8:30.

moshe,ruth04@mscc.huji.ac.il from israel wrote (2004-04-16 20:26:53 CET):

a question
hello
i am trying to use your example and create a vba function to turn
decimal value to time values.can you help?
Function hourto(time As Long)
hourto = TimeSerial(Hour(time), Minute(time))
End Function