ERLANDSEN DATA CONSULTING Excel & VBA Tips

# 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