Excel: Converting Time Entered as a Number to a Valid Time

ID: Q78933

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

SUMMARY

The formula shown below converts time entered, without the colon (:) (for example, 121010 instead of 12:10:10), to a valid serial number so that time calculations can be performed or number formatting applied.

MORE INFORMATION

Example

Enter the following number and data into a worksheet.

A1: 95111
A2: =VALUE(LEFT(A1,LEN(A1)-4)&":"&MID(A1,LEN(A1)-3,2)&":"&RIGHT(A1,2))
```
   The value, 0.410543981, which is a valid serial number, should
   result in  cell A2. 
```

Select cell A2. From the Format menu, choose Number. Select the "hh:mm:ss" format.

Now, cell A2 should display the value 9:51:11.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 137-138, 151-152, 200, 243

Additional query words: 2.10 2.1 2.10c 2.1c 2.10d 2.1d 2.20 2.2 2.21 4.0 4.00a

Keywords :
Version :
Platform :
Issue type :