Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Integer Times

 2000-02-04    Time    0    237

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