Time
Posted by Steve on June 15, 2001 10:13 AM
Can anyone tell me how to format a cell for time.
We want to enter just the 4 numbers (i.e. 1750) and
Have it auto format to time (i.e. 17:50)
We do not wan to type the colon.
Thanks
Posted by thomas venn on June 15, 2001 11:59 AM
this is not possible because when Excel looks at 1750, it is using 1750 as its serial number. Excel uses a serial numbering system for time. to get what you want, you might try something like this. for example: say you input 1750 in cell A1, then in cell B1, type in the formula =LEFT(a1,2)&":"&RIGHT(a1,2) --- The only problem is that the result is text and not a number.
Cheers,
- thomas
Posted by Mark W. on June 15, 2001 12:00 PM
You could format it as: #0":"00 ...however, your
value (e.g., 1750) would be stored internally as
the integer (1750) rather than a time value. To
use this value as a time you'd need to convert it
using the formula,
=LEFT(A1,LEN(A1)-2)/24+RIGHT(A1,2)/1440
...where cell A1 contained your value.
Posted by Tyler Hart on June 15, 2001 12:14 PM
Good idea but the text can be coverted to number by using this =TimeValue(Left(a1,2)&":"Right(a1,2)) then just format the cell for Time.
