Integer Times

 2000-02-04    Time    0    72

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.